Create Pivot Table or 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.

pivotortablemultiplefiles04

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

pivotortablemultiplefiles01

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.

pivotortablemultiplefiles05

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.

pivotortablemultiplefiles02

I also added a Clear Sheet button, to remove whatever is on the sheet, in case you want to start fresh.

pivotortablemultiplefiles03

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.

_____________________

4 comments to Create Pivot Table or Excel Table from Multiple Files

  • S K Srivastava

    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

  • S K Srivastava

    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

  • Todd Stone

    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?

  • Pradip

    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.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>