Import all Excel sheets (2003)

Topic

New Reply

post 686362 tells me how to reference a specific sheet to import, but I need to import ALL sheets from a workbook, but I do not know their names in advance.

Is there a way to work around that?

Thanks!

Reply | Quote

Replies

I love it when I can solve my own questions….

Sub ImportAllTabs()
    Dim strPath As String
    strPath = "C:PathToFiles"
    
    Dim objXL As Object
    Set objXL = CreateObject("Excel.Application")
    Dim strFileName As String
    strFileName = Dir(strPath)
    While strFileName  ""
        Debug.Print strFileName
        'Open the spreadsheet and get the tab names...
        With objXL
            .Workbooks.Open Filename:=strPath & strFileName
            
            Dim arrSheetName() As String
            ReDim arrSheetName(objXL.Sheets.Count)

            For x = 1 To objXL.Sheets.Count
                arrSheetName(x) = objXL.Sheets(x).Name
            Next
        End With
        objXL.Workbooks.Close
        'Import the tabs from this spreadsheet
        For x = 1 To UBound(arrSheetName)
            Debug.Print vbTab & arrSheetName(x)
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableToImportTo", _
                strPath & strFileName, True, arrSheetName(x) & "$"
        Next x
        'Next spreadsheet
        strFileName = Dir
    Wend
    
    objXL.Quit
    Set objXL = Nothing
    
End Sub

Reply | Quote

Thanks for sharing the solution!

Reply | Quote