AndyC1000
asked on
How to automatically import sheets from excel into access database
Dear all,
I've been manually importing data from an excel workbook into an access database using External data -> Import. There are over 20 sheets of data is there a way to automatically set up an import procedure that will iterate through the sheets using all the import wizard default options?
Thanks
I've been manually importing data from an excel workbook into an access database using External data -> Import. There are over 20 sheets of data is there a way to automatically set up an import procedure that will iterate through the sheets using all the import wizard default options?
Thanks
ASKER
Hello,
I've tried the example from the first link. I'm having troubles with the table name argument I would like this to be the same as the excel sheet name. The current error "action or method requires a table name argument".
Thanks
I've tried the example from the first link. I'm having troubles with the table name argument I would like this to be the same as the excel sheet name. The current error "action or method requires a table name argument".
Sub ImportAllSheets()
Dim objXL As Object
Dim sTable, xlPath As String, i As Integer
xlPath = "C:\Export\TestBook1.xlsx"
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Open xlPath, , True
With objXL
For i = 1 To .Worksheets.Count
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, sheet1, xlPath, True
Next
End With
objXL.Quit
Set objXL = Nothing
End Sub
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://www.experts-exchange.com/questions/23537693/How-do-I-import-Multiple-Excel-Worksheets-into-Access-and-update-an-added-field-with-the-worksheet-tab-name.html?anchorAnswerId=21928462#a21928462
https://www.experts-exchange.com/questions/23537693/How-do-I-import-Multiple-Excel-Worksheets-into-Access-and-update-an-added-field-with-the-worksheet-tab-name.html?anchorAnswerId=21930349#a21930349