Macro Creates Excel Pivot Table From Multiple Files

Pivot Table From Multiple Files

If you want to create a pivot table from data on different worksheets, you can use a Multiple Consolidation Ranges pivot table. However, that creates a pivot table with limited features and functionality.

Last year, Excel MVP Kirill Lapin (aka KL) shared his brilliant code to create a Union query and build a fully functional pivot table from data on different worksheets.

More Pivot Table Macros

Now, Kirill is back to share two more pivot table macros, and I’m sure you’ll be impressed by both of them. In today’s example, he’s written a macro to create a pivot table from multiple Excel files.

This is based on a solution that Kirill posted in one of Russian-speaking Excel forums for generating a standard pivot table from multiple workbooks (as opposed to worksheets in the same workbook).

The Report Workbook

The main file is named Report.xls, and it contains Kirill’s pivot table code, and a button that runs the macro. When you open the file, enable the macros, then click the Create Pivot button.

pivotwkbk01

The Data Files

When you run Kirill’s macro, it prompts you to select one or more data files, all stored in the same folder. In this example, the files for Alberta, Ontrio and Yukon are selected.

List of Data Files
List of Data Files

All of the data files must have the same structure, and the macro works with the data stored on Sheet1 in each file. In the screen shot below you can see Sheet1 in the Alberta and Ontario files, which have identical column headings.

pivotwkbk03

The Pivot Table

After you select the files and click Open, the macro creates a pivot table based on the data from Sheet1 in each of the selected files. Because the pivot table is 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.

SQL string for the Union query
SQL string for the Union query

Download the Sample File

To see Kirill’s pivot table code, you can download the Pivot Workbooks example. 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.

Pivot Table Macro #2

Come back on Wednesday to see Kirill’s second pivot table macro. It’s another creative twist on creating a pivot table from data in different workbooks.
______________

34 thoughts on “Macro Creates Excel Pivot Table From Multiple Files”

  1. Excellent! This is just the kind of routine I was planning on building.

    The only difference in approach is that I want to update an existing PT rather than creating a new one. Thanks Debra and Kirill.

  2. Thanks to you and Kirill for this and the previous, multiple worksheets, post that I hadn’t seen before. He makes it look so easy! I was wondering why the first part of the SQL Union query string only refers to “Sheet1? not the full workbook path. It seems to be that the DBQ argument to the the connection string refers to the full path and so it’s not needed for the first workbook. Just for fun, I modified the code so that the first Sheet1 also has the path, like this:

    If strSQL = “” Then
    strSQL = “SELECT * FROM ‘” & arrFiles(i) & “‘.[” & strSheet & “$]”
    Else
    strSQL = strSQL & ” UNION ALL SELECT * FROM ‘” & arrFiles(i) & “‘.[” & strSheet & “$]”
    End If

    and it also works.

    Great stuff!

  3. Not specific to Kirill’s multi-book query, but to the SQL query method: I am discovering that this technique requires stronger data typing than is common in most (of my) Excel sheets. Specifically, its important to maintain data types in the same column.

    Any advice for managing this??

  4. Hi AlexJ,
    The solution would greatly depend on what the actual data type mix you have. Can you give a specific example of the data types you might have in a given column of a source table? Except for the typical combination of any single type with NULL, I can’t imagine any mix that would make sense from the top of my head. Also, the question would be: how do you intend to use mixed data types in a Pivot Table (with or without SQL) in the first place?
    Reagrds.

  5. Debra, excellent post !!

    Kirill, great job !!

    I have 2 or 3 sets of data, stored in different workbooks, all with the same structure (SMS reports).
    this tool allowed me, with a litle tkweak, to combine them in a single PT, extremely useful.
    I had to add a previous refresh for the selected data source files, as they are web queries, but it work smoothly.

    My question, now. Is there a way I can combine multiple web queries into a single one, to use it as a unique data source for that PT?
    the reports are like this_
    http:///Report.asp?ReportID=170&os=Microsoft Windows XP Professional&sp=%25&CollectionID=AM000075

    the only change is the Collection ID. And no, there is no way to ask the SMS admin to create a new report with multiple IDs….

    Thanks !!!

    Martin

  6. Great work! The new approach has the big advantage that the datasheets for the pivot table are not in the same file. This enables to use dynamic spread sheets with ODBC interfaces which can be updated. The former approach has destroyed sometimes the data source.

    In order to use Excel 2007 files I have changed xls to xlsx in the VBA programming. That has caused an errormessage, but after ending this message and not trying to debug (!) the pivot table works perfect.

    Thank you very much for providing such a mighty tool!

  7. many thanks to Kiril to have developed this. I manage to adapt it to my needs except one small thing:
    I have some calculated fields in my data set (formulas elaborating the row data inputed), and that prevents the pivot table from correctly recognizing the data format as numbers and therefore all I get are zeros or no values at all.
    If I replace the formulas with pure numbers it works perfectly.

    Any solutions jumping to mind?

    Many thanks in advance

  8. I have the same question as Matthew. I have a workbook template that contains approximately 15 worksheets gathering different types of information and is distributed to approximately 100 different locations. This template is then summarized in another workbook with manual links to each and every other workbook…very cumbersome. This solution appears to be perfect. I would want to create a different pivot table for each worksheet – not one combined pivot table. Your thoughts?

  9. Dear Debra,
    I have a problem with the calculated field. Everytime when I try to do a calculated field to the pivot table, the values come out either 0 or just calculates the first column only. I am doing simple calculations only. (=A+B-C+D-E)

  10. I cannot get this to work with XL2007. The only changes I made were to save the example data files as xlsx, save the report file as xlsm and change .xls in the code to .xlsx in the two places .xls occurs (arrFiles = …). Can anyone help? I would love to be able to use this!

  11. Hi
    I can get this to take information from one workbook but when I select 2 or more it says ‘Cannot group that selection’
    Section that it asks me to debug is: .DataRange.Cells(1).Group _
    Start:=True, _
    End:=True, _
    Periods:=Array(False, False, False, False, True, False, True)

  12. I keep getting this message can anyone help asap:
    “Excel cannot complete this task with available resources.Chose less data or close other applications.
    I only had excel open no other application.
    Please help me
    Yasin

  13. The code is really great. However when I try to create a pivot from multiple data tables with 132 columns ist stops with an error.
    The tables have identical structure and the same column headings.
    The error: 1004: [Microsoft][ODBC Excel Driver] Too many fields defined
    The debugger shows this line:
    Set PT = .CreatePivotTable(TableDestination:= rng (6, 1))
    Is there any chance to get around this limit when consoldating data tables from multiple files?

  14. I have a workbook template that contains approximately 6 worksheets and I want to create a pivot out of the first three. This solution fits my ideas perfectly but thats all very new for me and I am struggling to fit the SQL part right to that it refers to the different sheets.
    If there is any help, I would be very thankful.
    Regards,
    Kati

  15. @Kati…easy to fix, but first , a couple of questions:
    1. What are the names of the various sheets that you DO want in the pivot
    2. do the first 3 worksheets have exactly the same columns?

  16. The workbooks I want to use contain macros so they are saved as .xlsm. Although I changed the macro to *.xl* it errors. How can I make this compatible with .xlsm workbooks… The error message I receive is “External table is not in the expected format.” Thanks.

  17. I have same error as Harmut. Whenever I try to use worksheet with more than 6 columns, I am getting ODBC error. Any suggestion?

  18. i merged three different excel files into a single file using pivot table.in that file i got pivot table with records.but the Payment name column contains only numbers instead of name.i dont know why?Please assist me

  19. I’m having problems to run it in excel 2013 (win8-64)!!! it’s something about kernel32 dll. Can anyone help me??? tks!

    1. Try this 😉
      Replace
      Declare Function SetCurrentDirectoryA Lib “kernel32” (ByVal Path As String) As Long
      by
      #If VBA7 Then
      Declare PtrSafe Function SetCurrentDirectoryA Lib “kernel32” (ByVal Path As String) As LongPtr
      #Else
      Declare Function SetCurrentDirectoryA Lib “kernel32” (ByVal Path As String) As Long
      #End If

  20. Hi,
    I have data files that are generated by SAP and sometimes there is 1 sheet and sometimes 2 sheets. I was hoping there would be a way to modify your code so that:
    1. The pivot table is created in a new workbook (This could be accomplished by copying the PT after created.)
    2. The code is in a workbook by itself and not in the workbook with the data.
    I would appreciate any assistance.
    Thank you

  21. Many thanks for this work – most helpful. I do,however, have a query. Can I use named ranges in the macro, specifically at this line (say Range 1, Range 2, Range 3) and how do I express them? I have other data on the sheets that I do not wish to be used in the pivot table report and are outside the named ranges:
    arrSheets = Array(“sheet1”, “sheet2”, “sheet3”)
    Regards and thank you.

  22. Your code and solution is great; I just a simple question your source data has Date field and no Year field but in your pivot table you have Year in Columns and also Data… how you came with the Year column? If you can please send me the response to my email. Thank you so much in advance.
    Jaime

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published.

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