• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

create a pivot table from folder

ashraf.albasel

New Member
Hi, I am trying to create a pivot table from multiple workbooks in one folder and supposed to be updated once add a new excel file to this folder
your support is highly appreciated
 

NARAYANK991

Excel Ninja
Hi ,

Read this to start with.

https://www.contextures.com/xlPivot08.html

As far as automatically including any new workbook that is added to the folder is concerned , this will require VBA. It will not happen automatically ; you will have to run a macro which will then check for a new file and then add that workbook to the pivot table.

Since the coding is quite extensive , I doubt that anyone will be willing to do it for free.

Narayan
 

Chihiro

Excel Ninja
There are few methods depending on your Excel version.

If you have access to PQ (Get & Transform) and using Excel 2013 or later, this would be relatively simple.
https://support.office.com/en-us/article/combine-files-in-a-folder-with-combine-binaries-power-query-94b8023c-2e66-4f6b-8c78-6a00041c90e4

If you don't. Then it would require VBA as Narayank mentioned.

Basic steps:
1. Use ADO code to query all workbooks in given folder. See link for how to do this.
https://chandoo.org/forum/threads/merge-sheet1-from-multiple-workbook-using-single-connection.33746/

But instead of loading to sheet, you'd populate array. You'll likely need to merge header (Fields) with data array.

2. Use array to populate pivot cache and build your pivot from there. See link for sample.
https://chandoo.org/forum/threads/combine-two-table-via-vba-create-pivot-from-array.39351/

Specific details will depend on how your workbooks are structured and how consistent data structure is across all workbooks.
 
Top