Creating a pivot table with multiple sheets

[xlsaffer]

Hi
I am trying to create a pivot table using multiple sheets. I looking for some code that will replace the "consolidated ranges" in the Pivot Table Wizard. I am looking for code because I am writing a macro that will create a different number of worksheets in the Workfile, depending on which dataset I use. i.e File 1 may have 75 worksheets, whereas File 2 may have 120 worksheets. '

The ranges on each of the worksheets will be the same. Range("A2:Av48")

Any help or directions to other references will be gratefully received.

Thanks

 

[MartinL]

Hi, Martin. Please refer to post #23 earlier in the thread. Regards, F

Ahhh thanks Fazza, for coming back on this...

I have just seen that the article is also in the "Gurus gone wild book". So before I come back with another question I'll try and make sure I'm not asking a repeat question.

Martin

 

[MartinL]

Ok

It is getting there now...

Can you point me in the right direction for this:

Row Grand Totals = False (do not show)
The Pivot Data defaults appears to be Count, I want Sum.

Martin

 

[MartinL]

hahahahahahahaha

gottit

.PivotFields("Count of [fieldname]").Function = xlSum
        
        .RowGrand = False

 

[tightwad]

I have been using the following code to consolidate multiple worksheets from the same book. It worked fine until I got to 51 sheets, and now I get an error that the Query is too complex. The code I am using is:

Sub CreateConnection()
    Dim PT As PivotTable
    Dim PC As PivotCache
    Dim strFile As String
    Dim strFileTemp As String
    Dim strPath As String
    Dim arrSheets As Variant
    Dim strSQL As String
    Dim strCon As String
    Dim i As Long
    
    '   Sheets to consolidate
    '*****************************************************************************
    'arrSheets = Array("Ontario", "Alberta")
    
Dim ws As Worksheet
ReDim arrSheets(0)
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "SQL" Then GoTo 1
        If ws.Name <> ActiveSheet.Name Then
            arrSheets(UBound(arrSheets)) = ws.Name
            ReDim Preserve arrSheets(UBound(arrSheets) + 1)
    End If
1: Next ws
ReDim Preserve arrSheets(UBound(arrSheets) - 1)

' Sheets to consolidate
'*****************************************************************************
    
    If Val(Application.Version) > 11 Then
        DeleteConnections_12
        CheckFileFormat_12
    Else
        strFileExt = ".xls"
        lngFileFormat = xlNormal
    End If
    
    Application.ScreenUpdating = False
    With ThisWorkbook
        strPath = .Path
        strFile = .FullName
        strFileTemp = strPath & "\DBtemp" & Format(Now, "yyyymmddhhmmss") & strFileExt
        ActiveSheet.Cells.Clear
        .Worksheets(arrSheets).Copy
    End With
    
    With ActiveWorkbook
        .SaveAs strFileTemp, lngFileFormat
        .Close
    End With
    
    For i = LBound(arrSheets) To UBound(arrSheets)
        If arrSheets(i) <> ActiveSheet.Name Then
            If strSQL = "" Then
                strSQL = "SELECT * FROM [" & arrSheets(i) & "$]"
            Else
                strSQL = strSQL & " UNION ALL SELECT * FROM [" & arrSheets(i) & "$]"
            End If
        End If
    Next i
    
    strCon = _
        "ODBC;" & _
        "DSN=Excel Files;" & _
        "DBQ=" & strFileTemp & ";" & _
        "DefaultDir=" & strPath & ";" & _
        "DriverId=790;" & _
        "MaxBufferSize=2048;" & _
        "PageTimeout=5"
    
    Set PC = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    
    With PC
        .Connection = strCon
        .CommandType = xlCmdSql
        .CommandText = strSQL
        'MsgBox strSQL
        Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range("A16"))
        PT.Name = "TestPivot"
    End With
        
    With PT.PivotCache
        .Connection = Replace(strCon, strFileTemp, strFile)
    End With
    
    'Clean up
    Kill strFileTemp
    Set PT = Nothing
    Set PC = Nothing
    
End Sub

It appears to just be the Union code that is too much...should there be a limit? Is there a better way to manage over 50 sheets on one PivotTable?

 

[Fazza]

hi tightwad

I'm not familiar with the technique & can't advise on it. As it is a different method from that described elsewhere in this thread it might be best addressed in a new thread so that it will be noticed?

There are limits to the SQL, though I am not expert in them - suggest you google. One limit is the length of the SQL text but that is in excess of 65,000 characters IIRC so not a concern for your example. I have encountered the 'SQL is too complex' message in the past and worked around it - can't recall the specifics. One thing to try is using normal defined range names for the source data instead of worksheet names as I find that more robust. You might also try listing the field names instead of the global "SELECT *".

Regarding alternatives, I suggest you try the method described in this thread - it creates a recordset with the dataset and then assigns that recordset to the pivotcache's recordset.

With 50+ worksheets it sounds like best to try different set up. If you really had to keep so many worksheets, one way may be is to create an mdb file - MS Access need not be installed to do this - and loop through loading one table with the data from all the worksheets. Then query this mdb file. It (the mdb file) could be just a temporary file deleted after the pivot table is created.

regards, Fazza

 

[tightwad]

I tried using the MACRO posted at the beginning of this thread as well, but I get an error about "Syntax error in query, Incomplete query clause". I saw someone else had this same issue, but didn't see what the fix was.

 

[Fazza]

I tried using the MACRO posted at the beginning of this thread as well, but I get an error about "Syntax error in query, Incomplete query clause". I saw someone else had this same issue, but didn't see what the fix was.

I can't see where this was a problem previously. Please advise the post number in the thread. For example, I am replying to your post #46 and this is post #47. Thanks.

Also, for the data you have, please check the set up is as assumed in the example - the code will be specific to the set up. If you have a different set up, the code won't work.

cheers

 

[tightwad]

I can't see where this was a problem previously. Please advise the post number in the thread. For example, I am replying to your post #46 and this is post #47. Thanks.

Also, for the data you have, please check the set up is as assumed in the example - the code will be specific to the set up. If you have a different set up, the code won't work.

cheers

Post 18 seemed to have the same problem, but he didn't indicate what the fix was. The only part I am unsure of is the

    objRS.Open Join$(arSQL, " UNION ALL "), Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
    .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

, specifically the Excel 8.0 part.

 

[Fazza]

Post 18 doesn't have the same error message; whatever the problem was then was sorted out by using the earlier posted code. I think that is a 'red herring' wrt to the current difficulties.

Post #46's error message was to do with the SQL. I don't know how/if that has been addressed, or why it is now (post #48) thought that the only problem is 'the Excel 8.0 part'.

Excel 8.0 is in the connection string. I think one of the earlier posts will have a link to information on connection strings.

PS. Post #21 has a link for connection strings. F

 

[Fazza]

I see post #3 has the error message about the SQL, like your post #46.

It relates to the data set up being different from assumed (for the code). VBA code is inherently specific to the set up.

Suggest you either change the set up to match the assumptions or change the code to suit your set up.

Basically, the objective is to have the SQL like below to pull data from each data sheet. OK?

Replace Sheet1, Sheet2, etc by your real worksheet names. Or maybe better, use non-dynamic named ranges instead - see earlier in the thread.

SELECT * FROM [Sheet1$]
UNION ALL
SELECT * FROM [Sheet2$]
UNION ALL
SELECT * FROM [Sheet3$]
UNION ALL
SELECT * FROM [Sheet4$]
UNION ALL
SELECT * FROM [Sheet5$]