importing multiple worksheets from a single workbook

[munscher]

John Nurick posted the following about 2 months ago

Microsoft MVP Joe Fallon has posted the skeleton code below fo
importing data from all files in a folder. It assumes text files bu
will work for Excel worksheets if you replace the DoCmd.TransferTex
statement with the appropriate DoCmd.TransferSpreadsheet - provided tha
you only need to import one worksheet from each workbook and that al
the worksheets have the same name (e.g. "Sheet1")

If those conditions don't hold (e.g. importing multiple sheets from on
workbook) it's still possible but more difficult.

'CODE START

Private Sub btnImportAllFiles_Click(
'procedure to import all files in a directory and delete them
'assumes they are all the correct format for an ASCII delimited import
Dim strfile As Strin

ChDir ("c:\MyFiles"
strfile = Dir("FileName*.*"
Do While Len(strfile) >
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName"
"c:\MyFiles\" & strfile, Tru
'delete the file (consider moving it to an Archive folder instead.
Kill "c:\MyFiles\" & strfil
strfile = Di
Loo

End Su

'CODE END

 

[munscher]

If someone could fill in the skeleton code with an actual example, i think i could understand it much better.

 

[John Nurick]

Sorry, no time just now to write code or I'll be late for work<g>. You
need to use Automation to get the worksheet names from Excel, and then
call DoCmd.TransferSpreadsheet for each worksheet using a loop similar
to that in Joe's code.

This is a useful article giving the basics of controlling Excel from
Access:
http://www.mvps.org/access/modules/mdl0006.htm

Using that as a skeleton, the loop would be something like

Dim objWkSheet As Excel.WOrksheet

...
For Each objWkSheet In objActiveWkb.Worksheets
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "MyTable", _
"D:\Folder\File.xls", True, _
objWkSheet.Name
Next

I can't remember whether you need to append a ! to the worksheet name,
or something else.