I have a few dozen Excel workbooks in a folder, and I'd like to import all sheets in each workbook to Access as linked tables. I have the following macro that creates linked tables from every sheet within a specific workbook, but I'd like to apply this to all workbooks in a folder.
Could someone help me modify this code?
Code:
Sub ImportAllSheets()
Dim wkb As Excel.Workbook
Dim sht As Excel.WorkSheet
Dim xl As Excel.Application
Dim strPathFile As String
strPathFile = "C:\Users\etc..."
Set xl = New Excel.Application
xl.Visible = True
Set wkb = xl.Workbooks.Open(strPathFile)
With wkb
For Each sht In .Worksheets
Dim dataRange As String
dataRange = Replace(sht.Range("A1").CurrentRegion.Address, "$", "")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, sht.Name, strPathFile, True, sht.Name & "!" & dataRange
Next
End With
End Sub