To create a pivot table from data on different worksheets, you can use a Multiple Consolidation Ranges pivot table, but the result is a pivot table with limited features and functionality.
Previously, Excel MVP Kirill Lapin shared his macro for creating a standard pivot table from data on different worksheets.
Now Kirill has expanded that technique, and written a macro to build a pivot table from data in multiple Excel files.
Pivot Table Macro
In Kirill’s example, the main file is named Report.xls. It contains the pivot table code, and a button that runs the macro. After you start the macro, it prompts you to select one or more data files, all stored in the same folder.
All of the data files must have the same structure, and the macro pulls the data stored on Sheet1 in each file.
Standard Pivot Table
The macro creates a standard pivot table based on a Union query, and not built from Multiple Consolidation Ranges, it’s a fully functioning pivot table, and you can pivot, group and filter the data, as usual. You can also refresh the pivot table, to show the latest data in the source files, as long as those files stay in their original location.
The Union Query
Kirill’s macro creates a Union query to combine the data from all the selected files. If you open Microsoft Query, you can see the SQL string for the Union query, and all the data from the selected workbooks.
Download the Sample File
You can download the Pivot Workbooks example to see the sample code and create the pivot table. The zipped folder that contains the Report.xls file, and the five sample data files. Unzip the folder, and keep all the files in the same folder. When you open the Report.xls file, enable macros to run the code.
______________
Krill’s macro works well with Excel97-2003, but what about Excel 2007? I can’t get it to work just by changing “.xls” to “.xlsx” in the macro. Any ideas?
Same problem.. it doesn´t work on Excel 2007.. any updates on this..
Thanks..
In the code, try the following changes:
''14-Mar-2012 changed extension to *.xls* arrFiles = Application.GetOpenFilename("Excel Workbooks (*.xls*), *.xls*", , , , True) ''14-Mar-2012 changed driver id from 790 to 1046 ''added driver info and removed DSN strCon = _ "ODBC;" & _ "DBQ=" & arrFiles(1) & ";" & _ "DefaultDir=" & "" & ";" & _ "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _ "DriverId=1046;" & _ "MaxBufferSize=2048;" & _ "PageTimeout=5"Wonderful Debra – thanks heaps – saved me a headache.
Hurray – this is excellent! Thank you very much.
Debra, any idea what changes would be necessary for Excel 2010? I’ve tried the 2007 changes but no joy,so I’m guessing that maybe there is a different driver id?
Many thanks if you can help. Kevin
@Kevin, the code works for me in Excel 2010, on both a WinXP and Win7 machine. I’m using 32-bit Excel though, so maybe something is different if you’re using 64-bit.
Change Declare statement to below
Declare PtrSafe Function SetCurrentDirectoryA Lib “kernel32” (ByVal Path As String) As Long
Replace declare statement to below
Declare PtrSafe Function SetCurrentDirectoryA Lib “kernel32” (ByVal Path As String) As Long
Hi Debra, many thanks for your prompt reply. Yes, I am using 64-bit. I’m also pretty computer-literate (IT background); can you give me any clues as to where I should search for the necessary info? Kind regards, Kevin
@Kevin, I don’t know much about the drivers, but you could ask in one of the tech forums on the Microsoft website, and someone might be able to point you in the right direction.
Debra, I’ll give that a try. Thanks again, I’m sure I’ll be back before too long now that I’ve discovered your site, it’s brilliant! I used to be accounted as something of a spreadsheet expert back in the days of Lotus 123 and I’m no beginner when it comes to Excel but I’m slightly rusty in one or two of its outer reaches. Your articles have already reminded me of several things I had forgotten and given me new insights as well. Keep up the good work, it’s much appreciated.
Hi Debra, after much research, head-scratching, and good old trial-and-error, I’ve discovered it works for me if I put the DSN back in!
However, I now have a new problem. Instead of creating a PivotTable directly from the Union Query, I need to create a Table in Excel containing all the data that is pulled together by the Union Query. I can do this manually but I’m having trouble coding it in VBA (about which I know virtually nothing – I’m trying to draw on previous macro-writing experience in other applications). Where I’m having trouble is in declaring the two variables (PT and PC) and their accompanying SET statements. Can you help? Regards, Kevin
Thanks for this code, the update on 14th of march works on my excel 2010 edition!
Another question I hope I can get help wth, or at least a pointer in any direction…
I would need to have the filename included in the columns of the pivot table. This information is however not available in the actual data within each worksheet so it becomes quite complicated…
I was thinking of adding a column in each separate excel sheets at the same time as you are creating the sql string (at this stage you have the file name available and no sql statement has actually been done), don’t know if this will be too slow or if there is a more elegant solution…?
Thanks for any help (and thanks for the helo so far).
Regards
Robert
You could amend the creation of the SQL string to something like this:
For i = 1 To UBound(arrFiles)If strSQL = "" Then
strSQL = "SELECT *, '" & arrFiles(i) & "' As FileName FROM [" & strSheet & "$]"
Else
strSQL = strSQL & " UNION ALL SELECT *, '" & arrFiles(i) & "' As FileName FROM `" & arrFiles(i) & "`.[" & strSheet & "$]"
End If
Next i
This would return the full path and file name, but will also introduce a new field so you would need to amend the default set up of the PivotTable as that code section will throw an error. You could get just the FileName by creating a custom getFileName function.
This is a very handy macro, so thanks to Krill for its development and Debra for sharing.
Hi Andrew,
Thank you for your code, this is what I am looking for, but there is little change . Can you please ammend this code to deliver only the File name and not the Full Path
Is there a limit on the size / number of rows this macro can handle?
I have 16 files, each with 64,000 rows. When I run the macro updated with Debra’s March 14 code, i get an error saying “system resources exceeded.”
When I run the macro on only two of the files, (so a total of 128,000 rows), then I get an error saying “unable to set the orientation property of the pivotfield class”, and when i click “debug”, it highlights the line “.Orientation = xlRowField” in the code.
Any idea on how I can fix this?
In this second case, it still does create a pivot table, but I’m not sure if it has done it correctly, or what is the problem. (when I try to do it for all 16 files, it doesn’t create anything)
Many thanks for the help,
Rob
Hi,
I have the same issue as yours, did you find a solution to your “system resources exceeded” error message?
Hi,
Excellent Macro. Thanks a lot. Really saved my day. I though have a question.
In order to run the macro and actually get the data, should the source files be closed and if not what should we do to run the macro and not get a connection lost error?
thanks in advance,
Dimitar
Hi,
I use the above macro that combines two different worbook from diferent database.
My problem is all the field names that have now changed in the database still appear below the old ones on the pivot tables and I can’t get rid of them to pick the new name only.
How do I ensure that the pivot table only reflect the new data field existing on the database?
Thanks!
Cel
You can also set this up to pull in multiple sheets by making a slight adjustment to the MergeFiles sub as per the below. Note that you put your multiple sheetnames in this form:
strTabs() = Split(“Sheet1;Sheet2;Sheet3”, “;”)
…i.e. list them with a semicolon between each one, with no spaces.
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
Dim strTabs() As String
Dim lngTabs As Long
strPath = CurDir
ChDirNet ThisWorkbook.Path
arrFiles = Application.GetOpenFilename("Excel Workbooks (*.xls), *.xls", , , , True)
strTabs() = Split("Sheet1;Sheet2;Sheet3", ";")
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 lngTabs = 0 To UBound(strTabs)
strSheet = strTabs(lngTabs)
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
Next lngTabs
strCon = _
"ODBC;" & _
"DSN=Excel Files;" & _
"DBQ=" & arrFiles(1) & ";" & _
"DefaultDir=" & "" & ";" & _
"DriverId=790;" & _
"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) 'Rep
.Orientation = xlRowField
.Position = 1
End With
.AddDataField .PivotFields(8), "Sales", xlSum 'Total
With .PivotFields(3) 'Region
.Orientation = xlPageField
.Position = 1
End With
With .PivotFields(2) 'Date
.Orientation = xlColumnField
.Position = 1
.DataRange.Cells(1).Group _
Start:=True, _
End:=True, _
Periods:=Array(False, False, False, False, True, False, True)
End With
End With
'Clean up
Set PT = Nothing
Set PC = Nothing
ChDirNet strPath
Application.ScreenUpdating = True
End Sub
Could anyone help me with the coding of actually getting the macro to create pivot tables from within the opened worksheets such as, sheet2 + sheet3. Instead of getting the worksheet from within a specific folder.
Hi,
How could I modify this code so that it opens a single file (not through filepicker) and creates a pivot table from all the sheets in the workbook but on a new workbook?
Any assistance would be greatly appreciated.
Walter
Hi Team,
I Need macro to extract data from excel and create Pvarious Pivots. also if i can name the different pivot tabs it would be of great help.
i tried to record one macro to create pivot but it dint work and gave below error, please need your urgent help:
Sub Macro3()
‘
‘ Macro3 Macro
‘
‘
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
“Actual Time booking- Nav!R1C1:R2134C8″, Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:=”Sheet8!R3C1″, TableName:=”PivotTable3” _
, DefaultVersion:=xlPivotTableVersion14
Sheets(“Sheet8”).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables(“PivotTable3”).PivotFields(“Work Item Set”)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables(“PivotTable3”).PivotFields(“Resource”)
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables(“PivotTable3”).PivotFields(“Work Item”)
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables(“PivotTable3”).AddDataField ActiveSheet.PivotTables( _
“PivotTable3”).PivotFields(“Actual Hours”), “Sum of Actual Hours”, xlSum
With ActiveSheet.PivotTables(“PivotTable3”).PivotFields(“Time Per”)
.Orientation = xlColumnField
.Position = 1
End With
Sheets(“Sheet8”).Select
End Sub
Hi there,
Thanks for the codes and I can successfully modified and ran the code for my own data including pivot table created without any problems. However, when I play around with the pivot table I’ve found one of my data source has changed to text which originally in number format. Is there a code I can add to these so I can convert the text column back to my number column in my exiting database in ODBC?
Many thanks
Ken
I would need a way to specificy a range so that not all data from the entire sheet is used. How and where do I do this?
Thanks in advance!
Matt, did you ever get an answer to that question about specifying a range? Trying to do the same. Thanks
Change Declare statement to below
Declare PtrSafe Function SetCurrentDirectoryA Lib “kernel32” (ByVal Path As String) As Long