How pull data from Excel by PivotTable number and worksheet name.

[JerryTheConsolidator]

Hello,

I have data to pull on excel workbook. I need to pull from sheet1 on these two Pivot Table# 1 and 2.

Can someone tell me how to do this or where I can get this info please?

Thanks

Jerry

[cjaneczko]

Can you provide an example of your data set? Without seeing it, you can use the input tool and set the range on the tool to capture only the cells the pivot table resides in. Then add an additional input tool for the other pivot table selecting the other range of cells. There are ways to bring both into one tool and then parse the data in a workflow, but we'd need to see what the data looks like to better answer your question.

[ScottLewis · Accepted solution]

PivotTableNumber (Actually PivotTable Name but the default is PivotTableNumberX) is part of the Excel Object Model, which Alteryx doesn't see on import.

There are a couple ways around this, but they assume you have the ability to modify the Excel file.

The most straightforward way is to give each Pivot Table its own tab. Then you just bring in the whole tab, trim blanks rows/columns as needed, maybe do a first rows as labels or somesuch and you have a workable data set in Alteryx that will adapt to changes in the pivot. 

The fancier way would be some VBA in your excel workbook that assigned a named range to the pivot table. You could either run it manually or put it in the OnClose area of the workbook so it ran whenever someone got out of the book.

I recommend the first option if you can. Keeping Pivot Tables isolated from other data elements is good practice anyway because of their utter disregard for other spreadsheet elements.

Note that the above all assumes that your pivot tables will be changing on both rows and columns. If one or both are expected to be invariant then that's an easier case and we can talk about ways to solve that without modification.

[JerryTheConsolidator]

Hi Cjaneczko,

I am a Noobie on Alteryx and just started look at Excel for users video tutorial.  Based on your suggestion to use input tool and select the range.  I will try this.  Sorry did not provide a dataset to see.  I will work on this weekend.  I appreciate you responded and will try your method. :)😀

[JerryTheConsolidator]

Hi Scott,

Normally, I use VBA to pull data from these two particular Excel pivot tables everyday.  At work, we now have Alteryz and I am trying to learn how to do this only by this new system.  Your suggestion to use VBA pull in data from the two Pivot Table, then let Alteryz (Input Tool) take the rest of the way.  This made it clear and I have a better understanding.   After Excel VBA pulls in PT data, it will bring two tabs known as Sheet1 and Sheet2.  Last night,  I was frustrated.  Again thanks.  

I will work on this weekend when I have time to show you and Cjaneczko. 

Thanks

Jerry