Importing MS Excel workbooks with multiple worksheets

[Lauri]

Please help - I have 20 workbooks, some with as many as
100 worksheets, and need to import them into an Access
table. The formats are identical. Access only allows me
to import one worksheet at a time, which will take days,
and in Excel I can't figure out how to combine all the
worksheets into one without cutting and pasting. I'm
sure someone else must have done this before. Thanks!

 

[Joe Fallon]

Use TransferSpreadsheet in code and loop through a list (another Access
table??) of all the named ranges and then import all the files in the
folder.

Sample code for text files:

How to Import all Files in a Folder:

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 String

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

End Sub

 

[Bill]

Lauri,

I have the same problem. Did you ever make sense of this
reply. I have over 200 excel files to import... all
identical in format.

bill