Unanswered: Import excel 2003 file to access 2003 database
hi, I'm new here and new to vba...
i want to be able to import an excel file into access 2003 into an existing access table and want to prompt the user to select the excel file. I have been able to import the excel file automatically into access using the macro design toolbar, but it only imports a specific file
this is the code i have...any help would be appreciated
On Error GoTo Import_Excel_Void_Shipment_List_Err
I'm curious to see how this turns out for you. I was looking at doing this last week w/ the DoCmd.TransferSpreadsheet method, but ended up just creating a subform with the table and having the user paste into that. Then moved that data into another table and clearing the original.
Good luck. If no one here knows how to do it I'll see if I can work through it with you. I'm sure lots of people would find the code useful. Will the excel file always have the same name and be in the same place, or are you going to have them browse to it?
This worked for me when I was playing around just now. It only imported the fields that have a value. The column header had to match the field name in the table or I got an error message, the order didn't seems to make a differene becuase I had left the first field in the table as the autonumber primary key, and it still put the data in the right place. Then again I only imported one column. I'm sure you could replace c:\test.xls with a string varible that your user assigns by browsing.
Another nice addition would be to verify that you have the correct tab.