Excell workbook sheets into Access

  • Thread starter Thread starter Steve Bliss
  • Start date Start date
S

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??
 
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
 
Back
Top