Unanswered: Importing Excel File to Access Database
I want to create a button in the form such that it will automatically transfer the excel data into the Access Table..
For eg. Excel files name CP file, I only one the first spreadsheet to transfer into the access. Another criteria, I want 6 excel files to transfer automatically into the access database with the click of the button.
The last criteria is that I want to delete the first row of the each spreadsheet before I want to transfer the 6 files into the access database..
There are some greate examples in the code bank which cover issues such as FSO (opening a dialogue box to prompt for file selection) and Excel manipulation (posted by pootle flump). These should cover almost everything you need.
What have I try is that I import the table by getting the external data.. However, I need to do some adjust in the data which is import to the Access table. First is the field name and second is the column 1 and row 1 of the data in the spreadsheet which I do not want. As for the 2 rows, it should be the field name of the access database.. I am not sure... How to do the automate part cause i am not an expert..
As for the code, I am also do not whether the DoCmd.transferSpreadsheet is suitable for my situation.
I would suggest trying to create a Macro to import the Excel spreadsheet. The macro editor will help you with the parameters for importing. Then it is much easier to figure out DoCmd.TransferSpreadsheet.
Another thought that I had is, you don't need to import the data. I typically link to a file, then use a couple of queries to append the data to existing tables. Or you can create a Make Table query to create the data.
If you are going to do it regularly (weekly) then I would suggest linking so you don't have to add the data twice to your database.
I agree with DCKunkle, I have a link to a spreadsheet that is updated each day. I append this linked table to a another table while appending I set the criteria to not append the record that has the header it could be "Employee name"