Problem importing/appending into Access 2007

[promark]

I have several large Excel files (with the exact same fields) that I need to import into one table in Access 2007. I have been trying for hours with little success. I even tried converting them to .csv and nothing seems to work.

Let's say I have two .xlsx files that I wish to combine into Access, each with the fields LAST NAME, FIRST NAME, and SALARY.

What I have been doing is this:
First I do an import of the first spreadsheet to create the table (since append doesn't seem to work if the table doesn't exist yet)

External data/Import/Excel/"Import Source Data..."

I then get the wizard, at which point I follow the prompts. I click the "skip field" box as necessary, for example skipping FIRST NAME. I then save my import steps and the table is now created (with the two fields I chose to import).

Now I go to create another import for the next spreadsheet, this time an append:

External data/Import/Excel/"Append a copy of the records..."

Following the prompts, I am never given the opportunity to skip the same field(s) that I chose in the first "table creation" import. As a result, I end up with messages such as "Subscript out of range" or "Field X not found".

Am I doing something fundamentally wrong here? From what I've described as my goal--import and combine multiple similar files--is the standard procedure to first do a "create" import and then an "append" import, or is there a way to append from the get-go?

Thanks!

 

[Insane_ai]

Try This: Import all of the fields when you crate the table. Import all the fields when you add the subsequent spreadsheets. Once all this is done, remove the field(s) you no longer need from the table.

 

[Peppy1]

I am experiencing the exact same problem as promark. The only difference is that I did not opt to skip any fields. The first set of data I import from excel 03 goes smoothly and I choose to have access create a primary key. When I try to add another spreadsheet to that table (all with the exact same field headers), I get an error back from access.

In both excel spreadsheets, the second column reads "Doc type of Ref Doc" but the error reads "Doc type of Ref # Doc" is not in destination table" I have tried going back and ensuring that an Alt+Enter is not causing this problem in excel. I even copied and pasted the first spreadsheet's headers onto the second spreadsheet so everything would be exactly the same but I'm still getting the error.

Any ideas on what I could be doing wrong?

Thanks in advance for your help.

 

[bdozier]

I had the same problem, first import ran fine, then ran it again and get the error about the field does not exist in the table. I read in help that the same field names MUST exist in the destination table as are in the spreadsheet. My workaround was to just import the table with the same field names as the excel file, then run another query in access to put that data into an existing table with the names I wanted ...