Excell workbook sheets into Access

[Steve Bliss]

I have a workbook withg 25+ pages, each set up the same. I
wish to import these into Access. They would all occupy
the same table.

Is there a way I can have them all imported in one motion
instaed of having to manually import each sheet
separately??

 

[John Nurick]

Hi Steve,

You can use VBA code to iterate through the worksheet names. This is
untested air code to show the idea:

Dim oWbk as Excel.Workbook
Dim oWks as Excel.Worksheet
Dim strFN As String

strFN = "D:\Folder\File.xls"
Set oWbk = GetObject(strFN)
If Not (oWbk Is Nothing) Then
For Each oWks In oWbk.Worksheets
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "My Table", _
strFN, , oWks.Name
Next
oWbk.Close False
oWbk.Application.Quit
End If