pivot tables and

[IanAgain]

I am trying to extract data from a pivot table with out success. I have tried the method outlined in the excel help but found it didn't work.

Dim pt As PivotTable
Dim dTemp As Double

Set pt = ActiveSheet.PivotTables(1)
dTemp = pt.GetPivotData("sum of cost", "Phase Number", 2)

"sum of cost" is the data field,
"Phase Number" is the field heading,
2 field item.

An error is generated "unable to get the PivotTables property of the Worsheet Class".

How do I get arround this.

I am also trying to get a list all all entries in a list so that I can hide them without generating an error.

Any help would be great.:banghead:

[rory]

Sounds like there isn't actually a pivot table on the active sheet.

[L@ja]

or, the pivot not show the requested fields...
getpivotdata get only the displayed fields
regards

[rory]

That error would occur with this line:
Set pt = ActiveSheet.PivotTables(1)

which implies there is no pivot table.

[IanAgain]

The error occours on the line:
dTemp = pt.GetPivotData("sum of cost", "Phase Number", 2)

I use:
Set pt = ActiveSheet.PivotTables(1)
successfully in other parts of the macro for things such as
.PivotItems(1).Visible = True
and formating type operations.

I seem to have no luck with extracting data.

The pivot table is from an other excel file which was exported by MS Office Project. The first part of the macro formates this file then copies the sheet with the pivot table in it. This sheet is then pasted into the file that is running the macro. The result looks like a pivot table and does request to update the data when the file is initially opened (which annoys me).

[rory]

I don't see how the error "unable to get the PivotTables property of the Worksheet Class" could occur on a line where you are not trying to access the pivottables property of the sheet. If the error were related to pivot fields I would expect a 1004 error or similar.

[IanAgain]

Rory,

Sorry to give you a bum steir, you are quite correct. I have tried many things with the pivot tables and I must have got the error message mixed up.

[rory]

GetPivotData returns a range object. You need something like:
Dim pt As PivotTable
Dim dTemp As Double

Set pt = ActiveSheet.PivotTables(1)
dTemp = pt.GetPivotData("cost", "Phase Number", 2).Value

[IanAgain]

Rory,

I have tried that with out success. I also tried setting dTemp as a variant and range to make sure that I didnt get the return type wrong.

I have put the 2 in the command as "2" and as 2 (as it is the entry I am looking for. I have specified for both "Cost" and "Sum of Cost" for the data field.

For a one line sub it is proving to be a real head ache. I feel that there is something stupid that I have got wrong.

[rory]

Can you post a stripped down copy of what the pivot table looks like? Also, which version of Excel are you using? I can't remember about the VBA method, but the GetPivotdata worksheet function syntax changed after 2000.

[IanAgain]

Rory,

A stripped down version of the file is attached.

The file was originally writen in V2003 but has had work done on it in
V2002.

thanks

[rory]

It's because you have custom subtotals set rather than the automatic ones - if you change them back to automatic, the code works. Related MSKB article here (http://support.microsoft.com/kb/284870); unfortunately, I cannot (yet) figure out if there is a syntax revision that will work in code...

[rory]

In the meantime, I've got it to work using GetData instead:
Dim pt As PivotTable
Dim dTemp As Double
Set pt = ActiveSheet.PivotTables(1)
dTemp = pt.GetData("cost 'Phase Number' ['2';sum]")

[IanAgain]

Rory,

The work sheet function in v2003 comes up as
=GETPIVOTDATA($A$1,"'Phase Number'['2';Data,Sum] 'Sum of Cost'"), once again this does not match the help files but it is easy to follow (once you know it). This may be reflected in vba. I'm yet to try it.


That kb article was very helpful and works for the worksheet formula.

One last question.... How do I turn off Automatic Subtotals.

[rory]

I tried every variation of that formula I could this morning and could not get it to work; I may try again tomorrow in case I missed a permutation! The GetData version does work though.
AFAIK, there is no way to turn that off. I just use code to do it.

[IanAgain]

Rory,

MS project exports the pivot table c/w Automatic Subtotals. I think I will just have too live with this limitation and fudge the result.

At least I can get the worksheet function "=GetData("cost 'Phase Number' ['2';sum]") " to work.

Thanks for all your help.
Ian