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.

______________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in Excel Pivot Tables, Excel VBA, Pivot Table. Bookmark the permalink.

19 Responses to Pivot Table From Data in Multiple Workbooks

  1. Michael Kaylen says:

    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?

  2. Carlos says:

    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"
      
  3. Mel says:

    Wonderful Debra – thanks heaps – saved me a headache.

  4. Michael Kaylen says:

    Hurray – this is excellent! Thank you very much.

  5. Kevin O'Kelly says:

    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.

  6. Kevin O'Kelly says:

    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.

  7. Kevin O'Kelly says:

    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.

  8. Kevin O'Kelly says:

    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

  9. Robert says:

    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

  10. Andrew Button says:

    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.

  11. Rob Larson says:

    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

    • LK says:

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

  12. Dimitar says:

    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

  13. Cel says:

    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

  14. Jeff Weir says:

    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

  15. TestPilot says:

    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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>