Inserting getpivotdata formula using vba macro
 

Hi,

I try to insert the following GETPIVOTDATA formula

Range("H7").Formula = "=GETPIVOTDATA(" _
& Chr(34) & "Hours" & Chr(34) & ";'complete pivot'!$A$1;" _
& Chr(34) & "OpAc" & Chr(34) & ";" & Chr(34) & "0190" & Chr(34) & ";" _
& Chr(34) & "Rec. Order" & Chr(34) & ";" & Chr(34) & "4711" & Chr(34) &
";" _
& Chr(34) & "Employee/app.name" & Chr(34) & ";" & Chr(34) & "John Doe" &
Chr(34) & ";" _
& Chr(34) & "Network" & Chr(34) & ";" & Chr(34) & "123" & Chr(34) & ";" _
& Chr(34) & "CW" & Chr(34) & ";" & "31" & ")"

If I leave out the leading equal sign before GETPIVOTDATA, the string is
correctly inserted. I only need to manually add the = in each cell and the
correct value is displayed.

If I try to run the above code including the leading = sign I get Runtime
"Error 1004 - Application defined or object defined error"

I know I can use GetPivotData directly in my macro, but I want to run the
macro just once and have Excel update the table automatically instead of
having to run the macro each time the underlying data base changes.

Any help on this would by greatly appreciated.

Cheers,
Alex

Inserting getpivotdata formula using vba macro
 

VBA likes to use the comma separator in formulas--no matter what your list
separator is.



AlexF wrote:

Hi,

I try to insert the following GETPIVOTDATA formula

Range("H7").Formula = "=GETPIVOTDATA(" _
& Chr(34) & "Hours" & Chr(34) & ";'complete pivot'!$A$1;" _
& Chr(34) & "OpAc" & Chr(34) & ";" & Chr(34) & "0190" & Chr(34) & ";" _
& Chr(34) & "Rec. Order" & Chr(34) & ";" & Chr(34) & "4711" & Chr(34) &
";" _
& Chr(34) & "Employee/app.name" & Chr(34) & ";" & Chr(34) & "John Doe" &
Chr(34) & ";" _
& Chr(34) & "Network" & Chr(34) & ";" & Chr(34) & "123" & Chr(34) & ";" _
& Chr(34) & "CW" & Chr(34) & ";" & "31" & ")"

If I leave out the leading equal sign before GETPIVOTDATA, the string is
correctly inserted. I only need to manually add the = in each cell and the
correct value is displayed.

If I try to run the above code including the leading = sign I get Runtime
"Error 1004 - Application defined or object defined error"

I know I can use GetPivotData directly in my macro, but I want to run the
macro just once and have Excel update the table automatically instead of
having to run the macro each time the underlying data base changes.

Any help on this would by greatly appreciated.

Cheers,
Alex

--

Dave Peterson

Inserting getpivotdata formula using vba macro
 

Thank you so much. One of these things you just have to know, I suppose...

"Dave Peterson" wrote:

VBA likes to use the comma separator in formulas--no matter what your list
separator is.



AlexF wrote:

Hi,

I try to insert the following GETPIVOTDATA formula

Range("H7").Formula = "=GETPIVOTDATA(" _
& Chr(34) & "Hours" & Chr(34) & ";'complete pivot'!$A$1;" _
& Chr(34) & "OpAc" & Chr(34) & ";" & Chr(34) & "0190" & Chr(34) & ";" _
& Chr(34) & "Rec. Order" & Chr(34) & ";" & Chr(34) & "4711" & Chr(34) &
";" _
& Chr(34) & "Employee/app.name" & Chr(34) & ";" & Chr(34) & "John Doe" &
Chr(34) & ";" _
& Chr(34) & "Network" & Chr(34) & ";" & Chr(34) & "123" & Chr(34) & ";" _
& Chr(34) & "CW" & Chr(34) & ";" & "31" & ")"

If I leave out the leading equal sign before GETPIVOTDATA, the string is
correctly inserted. I only need to manually add the = in each cell and the
correct value is displayed.

If I try to run the above code including the leading = sign I get Runtime
"Error 1004 - Application defined or object defined error"

I know I can use GetPivotData directly in my macro, but I want to run the
macro just once and have Excel update the table automatically instead of
having to run the macro each time the underlying data base changes.

Any help on this would by greatly appreciated.

Cheers,
Alex

--

Dave Peterson