Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Inserting a formula | Excel Discussion (Misc queries) | |||
Need help inserting formula with macro | Excel Programming | |||
Average from the GETPIVOTDATA formula | Excel Discussion (Misc queries) | |||
Help a Newbie! Using GETPIVOTDATA formula! | Excel Discussion (Misc queries) | |||
GETPIVOTDATA FORMULA | Excel Worksheet Functions |