VBA Consolidating Multiple Sheets in a Pivot Table

[ThomasB]

Hi

I got a brilliant bit of code (which works perfectly) from Bill Jelens "Excel Gurus gone Wild" which loops through all worksheets in the activeworkbook and consolidates these worksheets into one pivot table, this example creates the pivot in a new workbook, what I would really like to do is add a new sheet and create the pivot in the active workbook where I am pulling the data from can anybody help me to modify the code?

I have not added the code yet to generate the pivot

Any help would be greatly appreciated

Kind Regards

Thomas

Code below:

Option Explicit
Sub bob()

Dim i As Long
Dim arSQL() As String
Dim objPivotCache As PivotCache
Dim objRS As Object
Dim wbkNew As Workbook
Dim wks As Worksheet

With ActiveWorkbook
ReDim arSQL(1 To .Worksheets.Count)
For Each wks In .Worksheets
Do Until wks.Name = ""
i = i + 1
arSQL(i) = "SELECT * FROM [" & wks.Name & "$]"
Next wks
Set wks = Nothing
Set objRS = CreateObject("ADODB.Recordset")

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

Set wbkNew = Workbooks.Add(Template:=xlWBATWorksheet)

With wbkNew
Set objPivotCache = .PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = objRS
Set objRS = Nothing

With .Worksheets(1)
objPivotCache.CreatePivotTable TableDestination:=Range("A3")
Set objPivotCache = Nothing
Range("A3").Select
End With
End With
Set wbkNew = Nothing
End Sub

 

[MalcomT]

Hi there,

I used the example code in the previous posts for creating a Pivot table with VBA.

It works great for small numbers of worksheets (<20) but gives a "query is too complex" error (-2147467259 80004005).

With my limited understanding of SQL I think the UNION of SELECT * FROM filenames into the source field is too long.

Is there a way of constructing the query using sub-queries so that all my 140 sheets can be included?

Hope that someone has run into this problem too and can help.

Great forum, many thanks

Malcom

 

[MalcomT]

After a lot of reading over the weekend I discovered that SQL query limit is 65K and my query is 12K so it wasn't that.

It appears to be a limitation of the Jet 4.0 engine which only allows 99 AND statements in a single query!

Is there a way of using shorter queries and appending the data in the pivotcache?

Must be a workaround for this....

Thanks.

 

[cloudywaw]

Hi!

If I use the procedure shown in this post (copy below, procedure name "createPivotTable") to create a recordset from a worksheet or a named range, and then use the recordset to create and then populate a pivot table, what will the correct procedure be to update the recordset and then to update the pivot table if:

1. values in the existing data source change,
2. if the number of records in the existing data source changes,
3. if the structure of the existing data source changes (add, take away columns/fields)
4. if I need to add a second or third worksheet or named range to the data source?

I tried the procedure below (name "refreshPivotTable") but it does not always work, specifically:

1. if I add an additional worksheet or a named range to the data source, then the update procedure fails (the procedure does not recognise the name of the new data range). I then need to save the file, close Excel, reopen the file and rerun the procedure, at which time the new data range is taken into consideration and the pivot table refreshes correctly,
2. changes to the structure of the data source do not show after the pivot table is refreshed, i.e. new fields do not show in the pivot table, or they do show but the values which belong to this field do not show in the pivot table.

Therefore, is there a better way to refresh the pivot table? Am I doing this the wrong way?

Any tip will be greatly appreciated.

Tomek



Sub createPivotTable()
With ActiveWorkbook
arSQL = "SELECT * FROM [03-11$]"
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open arSQL, _
Join$(Array("Provider=Microsoft.jet.OLEDB.4.0; Data Source=", _
.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
End With
Set objPivotCache = .PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = objRS
Set objRS = Nothing
objPivotCache.CreatePivotTable TableDestination:=Sheet1.Range("A3")
Set objPivotCache = Nothing
End Sub


Sub refreshPivotTable()
With ActiveWorkbook
arSQL = "SELECT * FROM [03-11$] UNION ALL SELECT * FROM [04-11$]"
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open arSQL, _
Join$(Array("Provider=Microsoft.jet.OLEDB.4.0; Data Source=", _
.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
End With
Set ThisWorkbook.PivotCaches(1).Recordset = objRS
Sheet1.PivotTables(1).PivotCache.Refresh
Set objRS = Nothing
End Sub

 

[plumcloth]

I have similar situation where I am creating a Pivot form multiple worksheets. The pivot also is created as a separate sheet in the same file as other worksheets.Now how do I automatically refresh the Pivot table if any of the source worksheets are updated. Somehow I am not able to do it manually wither since the 'Refresh data' menu is greyed out. I would prefer auto refresh.

Hi Kiran

I realise your "Pivot Table Refresh" query was a few years, ago, but do you remember whether you resolved the issue of the "Refresh" being greyed out ?
I have the same problem

Thanks,

PD

 

[Saratoga]

Is there a way to use this consolidation code to select range of cells FI1:fu309 as opposed to the entire sheet?
Thanks,
Saratoga