Macro for importing from Excel

[ShawnR]

I have about 200 excel files that I need to import in Access (into one table). Is there a macro that can help do this? I have tried some I have found on other forums, but I keep getting error messages.

Thanks!

p.s.

I am a VBA novice

 

[Mile-O]

What's the naming convention of your Excel spreadsheets? Is there one?

 

[ashishprem]

Use this code to do the import:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "BUD_INPUT", STR, 1, "BUDGET!A"

where
STR is the excel file name.
BUD_INPUT is the access table name where the data needs to be imported
BUDGET: this is the sheet name.
"A" this says that it gonna import columns A to P. The same column naming conventions shoule be present in table.

1 - This says that the first records is a header.

Hope it helps.

Ashish