VBA to import all excel documents into a database

[jHopkin]

I need this to be done if possible. I want to be able to make a form, where it says the folder, then click Upload. It will look inside that folder, and import all the files inside into separate tables. The names of the tables will be based on as followed.
The name of the excel will be in this format:
A_B_C_D_E.xls.
The name of the table need to be in this format:
D_B_C_E

Once all the files are imported, it will display a box with the name of the excel document and the Table it got imported into. Or a table with the info will be great!

Please help me. Thank you very much!

 

[Isskint]

Step 1 is to use the FileDialog object to browse for the folder
Step 2 is to use the FileSearch object to loop through the folder for each excel file
Step 3 would be to use the TransferSpreadsheet method to import the Excel file
Step 4 would be to update a 'register' table with the new file details

 

[jHopkin]

Hi Isskint,
Is it possible for you to make me a code which I can just copy and paste into VBA? I have no prior knowledge of VBA, and what I know, are the extreme basic :S

 

[Isskint]

no one had any knowledge of VBA until they started using it. Play with it, abuse it (you wont get arrested).

I have attached a basic version of my suggestion for you to play with.

 

[jHopkin]

Hi, thanks for the code. I have tried to use it, but after I have chosen the folder, it displays

Run-time error '2455':
You entered an expression that has an invalid reference to the property FileSearch.

Pressing on Debug it takes me to this line: With Application.FileSearch

 

[Isskint]

Which version of Access are you using?

 

[jHopkin]

I am using Access 2010. Should it be a problem?

 

[Isskint]

Yes, filesearch was dropped from A2007 on. You need to find a replacement filesearch routine. There are several on the forum already. Try looking at http://allenbrowne.com/ser-59.html for some ideas.

I have attached a filesearch module i have put together, it may be of use.