Inserting getpivotdata formula using vba macro

  • Thread starter Thread starter AlexF
  • Start date Start date
A

AlexF

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
 
VBA likes to use the comma separator in formulas--no matter what your list
separator is.
 
Back
Top