A few years ago, Excel MVP Kirill Lapin shared his code to create a pivot table from identically structured tables in two or more Excel files. His technique used a Union query in Microsoft Query, and you can see the details here.
You just click the button to start the macro.
Then, select the files that you want to include (press the Ctrl key, and click on multiple files)
Pivot Table Created
A pivot table is created from all the data, and you can filter or sort the data, just as you would in any other pivot table.
Create an Excel Table Instead
Instead of building a pivot table from the data, a few people asked if it was possible to create a worksheet table instead.
So, I created a variation on Kirill’s macro, and I’ve uploaded a new version of the file. Now it has two “Create” buttons on the main sheet:
- one to create a pivot table, and
- one to create an Excel table.
Clear Sheet to Start Over
I also added a Clear Sheet button, to remove whatever is on the sheet, in case you want to start fresh.
Download the Sample File
To download the new version of the sample file, please visit the Excel Sample Files page on my Contextures site. In the Pivot Tables section, look for PT0033 – Pivot Table or Excel Table from Multiple Files
The zipped file contains a folder with region files and the master file – Report.xlsm. The master file contains macros, so be sure to enable those if you want to test the macros.
_____________________
First of all a big thanx to Debra for putting such an useful code. I found that the code was not generic and needed good amount of tweaking before it can be used in different cases. I am not very good in VBA but by some trial and error I have been able to create a more generic code for mergefilestable sub. Now you don’t have to worry about the sheet name. You will have to just name the ranges (to be merged) in all the sheets as “data”. Needless to say the data structure has to be same.
I tried to get a generic code for Pivot but realized that it may not be possible. In any case once you merge the tables creating a pivot over it is not a problem at all.
I hope it helps you all.
S K Srivastava
Sub MergeFilesTable()
Dim Lst As ListObject
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 ws As Worksheet
strPath = CurDir
ChDirNet ThisWorkbook.Path
Set ws = ActiveSheet
arrFiles = Application.GetOpenFilename(“Excel Workbooks (*.xls*), *.xls*”, , , , True)
strSheet = “.data data” ‘ Kindly ensure that the table structure is same in all the files and is named as data
If Not IsArray(arrFiles) Then Exit Sub
Application.ScreenUpdating = False
If Val(Application.Version) > 11 Then DeleteConnections_12
Set rng = ws.Cells
rng.Clear
For i = 1 To UBound(arrFiles)
If strSQL = “” Then
strSQL = “SELECT * FROM `” & arrFiles(i) & “`” & strSheet
Else
strSQL = strSQL & ” UNION ALL SELECT * FROM `” & arrFiles(i) & “`” & strSheet
End If
Next i
strCon = _
“ODBC;” & _
“DSN=Excel Files;” & _
“DBQ=” & arrFiles(1) & “;” & _
“DefaultDir=” & “” & “;” & _
“DriverId=790;” & _
“MaxBufferSize=2048;” & _
“PageTimeout=5”
‘
With ws.ListObjects.Add(SourceType:=0, _
Source:=strCon, _
Destination:=Range(“$A$4”)).QueryTable
.CommandText = strSQL
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
‘ .ListObject.DisplayName = “MyTable”
.Refresh BackgroundQuery:=False
End With
‘Clean up
Set Lst = Nothing
Set ws = Nothing
ChDirNet strPath
Application.ScreenUpdating = True
End Sub
Dear All,
In continuation to my earlier post:
I tried to tweak the code a little bit and make a more generic code for merging the data and making a pivot. The modified code is given below. This will replace the existing code for Sub MergeFiles. Everything else remains the same. The only care that is needed to be taken is that all the tables to be merged should be named “data”. They can be on any sheet. The column heads also need not start from first row which is needed in present code.
I hope you all will find it useful.
Regards
S K Srivastava
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 ws As Worksheet
strPath = CurDir
ChDirNet ThisWorkbook.Path
Set ws = ActiveSheet
arrFiles = Application.GetOpenFilename(“Excel Workbooks (*.xls*), *.xls*”, , , , True)
MsgBox (“Kindly ensure that the table structure is same in all the files and is named as : data”)
strSheet = “data” ‘ Kindly note that data is the name of the table
strSheet = “.” & strSheet & ” ” & strSheet
If Not IsArray(arrFiles) Then Exit Sub
Application.ScreenUpdating = False
If Val(Application.Version) > 11 Then DeleteConnections_12
Set rng = ws.Cells
rng.Clear
For i = 1 To UBound(arrFiles)
If strSQL = “” Then
strSQL = “SELECT * FROM `” & arrFiles(i) & “`” & strSheet
Else
strSQL = strSQL & ” UNION ALL SELECT * FROM `” & arrFiles(i) & “`” & strSheet
End If
Next i
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
Set PT = Nothing
Set PC = Nothing
ChDirNet strPath
Application.ScreenUpdating = True
Range(“A6”).Select
End Sub
Problem I am looking to solve is having multiple rows in pivot table that has multiple data sources. IE left rows = Employee name, type of leave time taken with each group subtotaled for that group, then all groups subtotaled for each employee, (ie vacation, sick FMLA Etc. type of leave time taken) then date such type of time taken by the employee, and then the columns will be each of the data fields the data for the employees is maintained in (ie: corporate payroll records for the employees, and onsite managers payroll records for the employee) those would be the two multiple data sources.
I can get it to work with simple pivot table with only one data source (ie the two data sources stacked togewther in one data base) then I can drag the type or the dates into row headers rather than columns. Multiple datasource pivot table does not allow the draging of date or type from column to row.
Also the date information is being totaled, comming up with unusual dates rather than listing each date an employee took a specific type of leave.
How do I create multiple row labels in a multiple data source pivot table? and how do I stop the multiple data source pivot table to stop totalling the date fields from the multiple data sources?
Hi ALL,
I have a query, what if the sheet names are different every time.. what code do i have to write.. for eg instead of sheet 1 if the sheet names in all files are different the how will it work…. and also if i want to select a range for all sheet.
Hi i have a query, if i am having a pivot with agency name and i want to import there individual performance to different sheet.for example
ie. If pivot has agency name A,B,C,D
i want worksheet 1: Agent A
worksheet 2: Agent B
worksheet 3: Agent C
worksheet 4: Agent D
@Varun, you can use the Show Report Filter Pages command to do this. There are instructions on my pivot table blog:
http://www.pivot-table.com/2012/05/02/create-multiple-copies-of-excel-pivot-table/
Note — These separate pivot tables are all linked to the original source data. If you want to send each person their data only, send a printed copy of the page, or a PDF file, or copy the data, and paste as values.
error:
external table is not in the expected format
Thank you
hi i have error (win7 + excel 2007)
https://youtu.be/5aYaZkiB1CQ
thank yoy