Pivot table using data from 89 workbooks (Excel VBA)

Question

I have 89 excel workbooks each contains 2 worksheets. Each work sheet represents one filling station. For the pivot table I would use only one of the two sheets. Front row is the same for each of them, but the number of rows is different-filling stations fill the data after delivery. At the moment there is not that much data (37 column and 100 row in each sheet)

I have set up an additional workbook with excel VBA code to pull the required data into one Pivot table.

Code works if I do not choose all of 89 workbooks. When I try to select all of them, there is an error message that says:

Run-time error '1004': [Microsoft][ODBC Excel Driver] Query is too complex

The debug shows:

Set PT = .CreatePivotTable(TableDestination:=rng(6, 1))

Can you please give some tips or advice to solve the problem? Thank you very much for any help.

Option Explicit

Declare Function SetCurrentDirectoryA Lib "kernel32" (ByVal Path As String) As Long

Sub ChDirNet(Path As String)
    Dim Result As Long
    Result = SetCurrentDirectoryA(Path)
    If Result = 0 Then Err.Raise vbObjectError + 1, "Error changing to new path."
End Sub



Sub MergeFiles()
    Dim PT As PivotTable
    Dim PC As PivotCache
    Dim arrFiles As Variant
    Dim strSheet As String
    Dim strPath As String
    Dim strSQL As String
    Dim strCon As String
    Dim rng As Range
    Dim i As Long

    strPath = CurDir
    ChDirNet ThisWorkbook.Path

    arrFiles = Application.GetOpenFilename("Microsoft Excel Macro-Enabled Worksheet (*.xlsm), *.xlsm", , , , True)
    strSheet = "DB"

    If Not IsArray(arrFiles) Then Exit Sub

    Application.ScreenUpdating = False

    If Val(Application.Version) > 11 Then DeleteConnections_12

    Set rng = ThisWorkbook.Sheets(1).Cells
    rng.Clear
    For i = 1 To UBound(arrFiles)
        If strSQL = "" Then
            strSQL = "SELECT * FROM [" & strSheet & "$]"
        Else
            strSQL = strSQL & " UNION ALL SELECT * FROM `" & arrFiles(i) & "`.[" & strSheet & "$]"
        End If
    Next i
    strCon = _
        "ODBC;" & _
        "DSN=Excel Files;" & _
        "DBQ=" & arrFiles(1) & ";" & _
        "DefaultDir=" & "" & ";" & _
        "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
        "DriverId=1046;" & _
        "MaxBufferSize=2048;" & _
        "PageTimeout=5"

    Set PC = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)

    With PC
        .Connection = strCon
        .CommandType = xlCmdSql
        .CommandText = strSQL
        Set PT = .CreatePivotTable(TableDestination:=rng(6, 1))
    End With

    With PT
        With .PivotFields(1)                            'Date
            .Orientation = xlRowField
            .Position = 1
        End With

        With .PivotFields(2)                            'Product
            .Orientation = xlRowField
            .Position = 2
        End With
            .AddDataField .PivotFields(32), "Manko", xlSum   'Difference N/V L15
            .AddDataField .PivotFields(9), "Sum of Dodané", xlSum   'Delivery L15
        With .PivotFields(16)                            'SPZ
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields(18)                            'supply
            .Orientation = xlPageField
            .Position = 2
        End With
        With .PivotFields(37)                            'Number of FS
            .Orientation = xlColumnField
            .Position = 1
        End With

    End With

    'Clean up
    Set PT = Nothing
    Set PC = Nothing

    ChDirNet strPath
    Application.ScreenUpdating = True
End Sub

Private Sub DeleteConnections_12()
     '*****************************************************************************
    On Error Resume Next: ThisWorkbook.Connections(1).Delete: On Error GoTo 0
    '*****************************************************************************
End Sub

Please check if your PC has a SourceData add a line Debug.Print PC.SourceData — Shai Rado

jeffreyweir jeffreyweir · Accepted Answer · 2017-04-26T20:14:03

The Microsoft JET/ACE Database engine has a hard limit of 50 ‘UNION ALL’ clauses, which you have exceeded. The only way around it is to create sub-blocks of UNION ALL statements, then stitch them all together with another UNION ALL. I demonstrate how to do this at the following link:

http://dailydoseofexcel.com/archives/2013/11/19/unpivot-via-sql/

Your other options are to bring the data from all the different workbooks into a master sheet by using VBA and then make a pivottable out of that (which will be much faster than using SQL statements as per the timings I posted at http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/ ) or to use PowerQuery, which will be the simplest approach by far.

Pivot table using data from 89 workbooks (Excel VBA)

1 Answers