Create Pivot Table or Excel Table from Multiple Files

Excel Table from Multiple Files

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.

worksheet button to run macro
worksheet button to run macro

Then, select the files that you want to include (press the Ctrl key, and click on multiple files)

select files to include
select files to include

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.

pivot table created from files
pivot table created from files

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.
worksheet buttons to run macros
worksheet buttons to run macros

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.

Clear Sheet button on worksheet
Clear Sheet button on worksheet

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.

_____________________

0 thoughts on “Create Pivot Table or Excel Table from Multiple Files”

  1. 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

  2. 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

  3. 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?

  4. 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.

  5. 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

Leave a Reply to Kemana Kusuka Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.