Import All Excel Workbooks in Folder as Linked Tables

[kestefon]

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?

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

[June7]

Requires looping through all items in folder.

Review

http://www.allenbrowne.com/ser-59.html

http://msgroups.net/microsoft.public...f-all-fi/82007

[kestefon]

Thanks, that helped.

One more thing: How would I close the Excel workbook after opening it and exporting to Access? I tried "Workbooks(filename).Close False", but got a subscript out of range error.

    With wkb
        For Each sht In .Worksheets
            
            Dim dataRange As String
            dataRange = Replace(sht.Range("A1").CurrentRegion.Address, "$", "")


            DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, sht.Name, filename, True, sht.Name & "!" & dataRange
            
            Next
    End With
    Workbooks(filename).Close False

[June7]

I've never used TransferSpreadsheet with an Excel object. Didn't know that was possible.

Maybe:

wkb.Close False
Set wkb = Nothing