Import Multiple Excel Files

[daniela]

Hello,

I need to import multiple excel files located in a specific folder. I am able to import one and then delete it from the location (see code below) but I want to be able to do the same thing for all the excel files in that folder.

I am a VBA beginner and have search all over this forum and online but no luck. I want to learn so you help would be appreciated!

Thanks!!!


Private Sub Command1_Click()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "updates_temp_tbl", "c:\temp_imports\latest_updates_karen.xls", True

Kill "c:\temp_imports\latest_updates_karen.xls"

End Sub

 

[BrokenBiker]

Can't you just put more of the same lines of code in? You know, just basically copy the code you already have, change the necessary info for the new file and the new table.

Private Sub Command1_Click()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "updates_temp_tbl", "c:\temp_imports\latest_updates_karen.xls", True
Kill "c:\temp_imports\latest_updates_karen.xls"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblNewTable", "c:\NewFolderLocation\NewExceltoImport.xls", True
Kill "c:\NewFolderLocation\NewExceltoImport.xls"

End Sub

 

[daniela]

I could but...

I can't because sometimes I get 15 reports other times 50, the all get imported to the same table. So if I do that for all 50 when a file(s) is missing one morning it will error out.

Do you know a way I can get all the files in the folder with out specifying each one?

Thanks ~ Daniela

 

[BrokenBiker]

Sorry, I sure don't.

It stands to reason that there should be a way to search for files, and if there is a file then return a value. Then maybe use something like If IsNull nameofsearchstring Then...end sub.

Or maybe you could set up a loop function with the loop counter as a variable that you 'plug' in...maybe based on a table. If you know the number of files to import, enter that amount in a table, then have the VBA draw that count.

VBA doesn't use wildcards for this, does it? I think because "c:\temp_imports\latest_updates_karen.xls" has quotations in it you can't say c:\Folder\*.xls

Wish I could be of more help.

You might want to keep an eye on this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=104540

 

[ghudson]

This should get you started...

importing multiple txt files into Access

Searching the forum is a great way to discover and learn the answers to your Access programming questions.