How do I import an Excel file with MULTIPLE WORKSHEETS into Access

[KramerJ]

How do I import an EXCEL FILE WITH MULTIPLE WORKSHEETS into Access 2003. I am
creating a master table in Access of archived Excel files, each file has
multiple worksheets. Is there a need to use both SQL and VBA to accomplished
the import. TIA

 

[Ken Snell MVP]

Do you know the names of the worksheets in the files? Or do you have to
"read" the worksheet names from the EXCEL files?

If the former is true, you can use the code at this web page as a starting
point. Just add another loop or extra steps for the various worksheets.
Import Data from All EXCEL Files in a single Folder via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpFolderFiles

 

[ryguy7272]

I tried to do this a few days ago; never figured it out. How do you "read"
the worksheet names from the Excel files?

I tried this, but it didn't work:
For i = 1 To Workbooks(MyWorkBook).Sheets.Count
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTablename, strPath & strFile, blnHasFieldNames
Next i

Here is the code that I was working with (this imported the first sheet in
each Excel file):
Sub ImportAllExcelFiles()
On Error GoTo Err_F
Dim strPathFile As String, strFile As String, strPath As String, strSpec As
String
Dim strTable As String, ynFieldName As Boolean
ynFieldName = False
strPath = "C:\Import\"
'strSpec = "NameOfImportSpecification" ' Put your name here
strTable = "tablename"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable,
strPathFile, ynFieldName
' Uncomment out the next code step if you want to delete the file after it's
imported
' Kill strPathFile
strFile = Dir()
Loop
Exit_F:
Exit Sub

Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F

End Sub

Again, it reads the first sheet, and only the first sheet, in each Excel file.
How can this be modified to read multiple sheets in each Excel file, and
also loop through all the Excel files in a folder?

Thanks,
Ryan---

 

[Ken Snell MVP]

Reading the worksheet names from the EXCEL file involves opening the EXCEL
file, storing the worksheet names that the code reads as it loops through
the worksheets, closing the EXCEL file, then putting a loop around your
import code for the list of worksheet names.

I've got some code to do this, which I wrote a while back.... let me find
it, and I'll post an example on my website. I've been meaning to do this for
a while, and I think I've got some time tonite to do that...so, I will!

 

[Ken Snell MVP]

OK here it is:

Import Data from All Worksheets in a single EXCEL File via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWkshts

 

[Ken Snell MVP]

Slight change in title, and an additional example:

Import Data from All Worksheets in a single EXCEL File into One Table via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWkshts


Import Data from All Worksheets in a single EXCEL File into Separate Tables
via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWktsSepTbl

 

[ryguy7272]

Thanks for the code Ken! Amazing, just amazing! I looked at your site a few
times before, but didn’t see that code there. Is it a recent addition to
your site?

You may, or may not, know about this link:
http://www.rondebruin.nl/copy2.htm

That is a great way to get data from all Excel sheets, in one workbook, into
a single sheet, which, then, is quite easy to import into Access. So,
anyway, just wanted to share with you, and others out there reading this.

Thanks again!!
Ryan---

 

[Ken Snell MVP]

That code is new to the site; put it up there last night. So it's no
surprise that you hadn't seen it before < g >.

Thanks for link to Ron's site.

 

[prashantpatole]

If you dont want to do this using any kind of program (manually).

Import the Excel into a SQL server blank database and then Export it to MS Acess.

You can do this in SQL Management studio. Right click on blank database-> select Task, and select Imort/Export.