Macro - import multiple excel sheets in one click

[kanelones]

Hi!

Maybe you can help me.

I just started using Microsoft Access, and need a little help.

I got several spreadsheets, named po(1).xls, po(2).xls, po(3).xls, a so on , to 150 files.
Need to import the data on those sheets to my table on Acces, but instead of repeating the process one at the time ( im using the saved imports screen option ) , wonder if i could repeat the process indefinitely, with an importing macro.
Can you help me to program it on access.
It will be very usefull for my daily work.

Thanks a lot for you help indeed.

Best Regards
Pablo

 

[welshgasman]

You could put them all in a folder ans use this?

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

HTH

 

[kanelones]

have trouble with this line

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

 

[kanelones]

Just worked perfect!

Now i need to fix other little detail...

each worksheet has 2 tabs.. need to eliminate the heather..( would be the first sheet ) any ideas to do that? ( this was REALLY helpfull!!!)

 

[kanelones]

need to delete one of the two sheets within the worksheet before the importation, have two sheets, "header" and "transations" the one i need to delete is named " header"
Can you be so great to show me the code to eliminate this sheet before ?
Thanks!

 

[welshgasman]

If you look at the command it has an option for Range, after HasFieldNames ?

Try "Transations"

In fact that code specifies worksheets as well, so just transfer what you need.

 

[kanelones]

Sorry, you mean this line?

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = "Transactions"

 

[kanelones]

All done! Worked just fine! will save me hours!
You are Genious,, i appreciate your help.

Best Regards!