I'm attempting to import data from an MS Excel spreadsheet. During the import process, the wizard is not allowing me to edit the field names, only the first one. Don't understand why I can't edit any other fields, or determine if I don't want to import other fields. Any suggestions as to why? Should I go about another way to import the Excel data? Looking for the easiest way to do this. It's not a lot of data (about 20 fields and 60 rows), but I will need to append to the table each month. It's a month production report saved as an Excel file. I would like to generate a report or two using Access. There is other data that will also be used.
Don't know if this helps or not, but the report is from the Red Prairie LMS utility.
I have never found a straight forward way around this. But, this idea works: Import the data just as it is, then write a query to take the data you want form this table and add it to the table where you will be reporting from. In the query you can take data from one field name and output it to the correct field name in the "report-from" table.
Another idea would be to link the Excel spreadsheet into Access (as a linked table), then using a query you could either report directly from the linked table, or if you are going to keep the data in Access, then write the data from the linked table to your Access table, again, with a query as in the first idea.
There are two other ways I have used. First, open the file in Excel then Save As a comma delimited file. Then in Access you will have much more control of the field names and data to import using a File Specification.
Or, you can open the Excel spreadsheet using VBA and export the data to a delimited file and then import the delimited file. I took this approach to completely automate importing Excel spreadsheets. But it is not easy, and it is not fast.