essaytee
Need a good one-liner.
- Local time
- Tomorrow, 06:02
- Joined
- Oct 20, 2008
- Messages
- 531
Obviously, I do have control in creating the spreadsheets for users, but the spreadsheets themselves will not be directly data entered, one row by one row. The idea is that the spreadsheet is provided as a template for the required data. Users will be using other spreadsheets received from external agencies and all will be in various formats. The information provided by external agencies, usually incorporates the information that is required, and our users will manipulate that data creating additional columns in the process. Some columns require narratives to be written, incorporating the data, usually including dates, times, amounts, locations etc. (think mail merge). Not all users will have the skills to achieve this, but some are capable and would rather spend a day or two with Excel as opposed to weeks of data entry.
The general advice is that once they have compiled or constructed all the columns, line them up in the same order as the template, then copy and paste as values to the template spreadsheet.
For the import routine Access will scan the spreadsheet, if there are any problems with the data, that problem is written to a text file so at least afterwards the user can check the error text file and directly check the entries of concern. This will be very useful if there are 1000's of records.
Initially, the import is to a temporary table, then some further error checking, and eventually appended to the live table and the temporary table records are deleted.
So, while the users arebutchering manipulating other spreadsheets, they do what they have to do. Come the final 'paste as values' to the templated spreadsheet, it's quite possible that column formats are also copied across and not quite as I had planned.
I am not an expert in all things Excel so if there are easy ways to prevent users from modifying column formats, without side effects, I'm all ears. In a previous comment above, I did mention that I trialed locking the row headings, and protecting with a password, but the side effect was that the users could not resize the rows or columns, and was a PIA.
The general advice is that once they have compiled or constructed all the columns, line them up in the same order as the template, then copy and paste as values to the template spreadsheet.
For the import routine Access will scan the spreadsheet, if there are any problems with the data, that problem is written to a text file so at least afterwards the user can check the error text file and directly check the entries of concern. This will be very useful if there are 1000's of records.
Initially, the import is to a temporary table, then some further error checking, and eventually appended to the live table and the temporary table records are deleted.
So, while the users are
I am not an expert in all things Excel so if there are easy ways to prevent users from modifying column formats, without side effects, I'm all ears. In a previous comment above, I did mention that I trialed locking the row headings, and protecting with a password, but the side effect was that the users could not resize the rows or columns, and was a PIA.