Pivot Table From Data in Multiple Workbooks

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.

pivotwkbk04

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.

pivotwkbk05

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.

______________

29 thoughts on “Pivot Table From Data in Multiple Workbooks”

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

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

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

      1. Change Declare statement to below

        Declare PtrSafe Function SetCurrentDirectoryA Lib “kernel32” (ByVal Path As String) As Long

      2. Replace declare statement to below

        Declare PtrSafe Function SetCurrentDirectoryA Lib “kernel32” (ByVal Path As String) As Long

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

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

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

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

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

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

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

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

    1. Hi,
      I have the same issue as yours, did you find a solution to your “system resources exceeded” error message?

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

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

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

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

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

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

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

  16. 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!

  17. Change Declare statement to below

    Declare PtrSafe Function SetCurrentDirectoryA Lib “kernel32” (ByVal Path As String) As Long

Leave a Reply to Jeff Weir Cancel reply

Your email address will not be published. Required fields are marked *

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