Link to home
Create AccountLog in
Avatar of Geo_Bout

asked on

Read data from all sheets of an excel file using OPENROWSET

I'am currently working on an SSIS project and I want to get  the data from all  sheets of
an Excell file (.xlsx)  that I need to import it into an SQL table periodically. I don't know each time the number of sheets its contains.
I know how to deal with one sheet  using for example
Select * from Openrowset (MicrosoftFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;IMEX=1;      Database=E:\export\file.xlsx',
                        'SELECT * FROM [sheet1$]'
Is there a clever way to get all the sheets? I suspect that somehow i must get the excelschema via script  to get the sheet name in an variable and then to use a For-Loop . If that so then could any one can help me with the script..
Thanks in Advance
Avatar of Rgonzo1971

Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Assuming all sheets have the same structure (columns), loop through the sheets with VBA to combine them into a single "master" worksheet and use the OPENROWSET statement once.  The code below creates a new worksheet in position 1 named "Master".  That name can be changed to whatever you want in the Initialize Variables section of the code.
Sub Combine_Worksheets()
    Dim i As Integer, j As Integer
    Dim lstRow1 As Integer, lstRow2 As Integer, lstCol As Integer
    Dim ws1 As Worksheet, ws As Worksheet
    Dim wsMstr As String
    Dim LstCell As String
    Dim EMsg As String
    Dim DisplayMsg As String
    Dim ColCnt As Integer
    Dim CntFlg As Integer
    Dim Response As String
    Dim errorMsg As String

    On Error GoTo errHandler
    errFlag = False

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .StatusBar = False
    End With

    'Intitialize Variables
    wsMstr = "Master"       'Master Worksheet Name - rename as needed
    EMsg = ""
    CntFlg = 0
    Application.StatusBar = "Creating/Updating the " & wsMstr & " Worksheet with Current Data"

    With Range("A1", ActiveCell.SpecialCells(xlLastCell))
    End With

    Set ws1 = Sheets(wsMstr)
    lstRow2 = 1
    j = 1

    'Check the Number of Columns in each data worksheet
    'Set CntFlg to 1 if any sheet has a different number of columns
    ColCnt = Sheets(2).Cells(1, 16384).End(xlToLeft).Column
    For i = 2 To Sheets.Count
        If Right(Sheets(i).Name, 1) <> "+" Then
            EMsg = EMsg & ActiveSheet.Cells(1, 16384).End(xlToLeft).Column & " Columns - " & Sheets(i).Name & Chr(10)
            If ColCnt <> ActiveSheet.Cells(1, 16384).End(xlToLeft).Column Then errFlag = True
        End If

    'IF CntFlag was set above, Display the Error Dialog and exit the routine
    If errFlag = True Then
        DisplayMsg = "The Component Sheets Do Not Have the Same Number of Columns" & Chr(10) & Chr(10)
        DisplayMsg = DisplayMsg & EMsg & Chr(10)
        DisplayMsg = DisplayMsg & "Correct the Sheets Before Continuing" & Chr(10) & "Routine is Exiting"
        Response = MsgBox(DisplayMsg, vbCritical, "wsMstr Column Count Error")
        GoTo finished
    End If

    'Combine the sheets
    For i = 2 To Sheets.Count
        If Right(Sheets(i).Name, 1) <> "+" Then
            lstCol = ActiveSheet.Cells(1, 16384).End(xlToLeft).Column
            lstRow1 = ActiveSheet.Cells(65536, "A").End(xlUp).Row
            Range("A" & j, Cells(lstRow1, lstCol)).Copy
            With ws1.Range("A" & lstRow2)
                .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                .PasteSpecial Paste:=xlPasteFormats
            End With
            Application.CutCopyMode = False
            lstRow2 = ws1.Cells(65536, "A").End(xlUp).Row + 1
            j = 2
        End If

    'Determine the Last Cell in the wsMstr worksheet
    LstCell = Worksheets(wsMstr).Cells.SpecialCells(xlCellTypeLastCell).Address

    With Sheets(wsMstr)
        .ListObjects.Add(xlSrcRange, Range("$A$1:" & LstCell), , xlYes).Name = "Tbl_Mstr"
    End With


    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .StatusBar = False
    End With

    Exit Sub


    'If the wsMstr worksheet does not exist a Subscript out of Range error will
    'occur. Create the wsMstr worksheet and return / continue
    If Err.Number = 9 Then
        Sheets.Add Before:=Worksheets(1)
        ActiveSheet.Name = wsMstr
        Resume comeBack
        errorMsg = "An error has occurred." & Chr(10) & Chr(10) & Err.Number & " - -" & Err.Description
        MsgBox errorMsg, vbCritical, "Error Message - " & ThisWorkbook.Name
        Resume finished
    End If

End Sub

Open in new window

you should just use SSIS foreach loop

see full  ready to use project , just adjust for yourself:
How to load data from multiple Excel sheets to any destination in SSIS
just be careful with some of the suggested solutions because some of the other suggestions require all sheets in the Excel file must have the same structure.
Avatar of Geo_Bout


Greate Feedback . I tested the two first proposals and worked fine . I do have the same structure for all sheets (represent sales from all substores of the same company. I retreived the table easily as proposed  by Mark and after using a Foreachloop it worked perfectly.
Just notice, that I tested the solution as proposed by Rgonzo i found out that  it works  in newer version than visual studio 2005 ('outputbuffer' if  Iam correct its not supported in VS2005).  
Thanks all of you  for your immediate responce