PxPlus User Forum
Main Board => Discussions => Language => Topic started by: thenewguy on November 06, 2019, 03:41:18 PM
-
Hello,
I'm messing with the VBA Excel commands within PxPlus. I'm having a moderate amount of success, but have run into a wall.
In my current test, I'm trying to create a pivot table.
I've managed to put all data into the sheet, but trying to create the pivot table gets me stuck. Here's the three relevant lines:
let TABLE_RANGE$=DATA_SHEET'NAME$+"!A1"+LASTCOL$+"1:A"+str(LASTROW)+LASTCOL$+str(LASTROW)
let TABLE_CACHE=EXCEL_OBJ'ACTIVEWORKBOOK'PIVOTCACHES'CREATE(1,TABLE_RANGE$)
let TABLE=TABLE_CACHE'CREATEPIVOTTABLE("Chart!A1")
It's specifically the last line. I'm able to create TABLE_RANGE$ and TABLE_CACHE without error, but it simply does not like any variables for CREATEPIVOTTABLE.
("Chart" is the name of the first sheet of the workbook)
Other things I've tried:
- excel_obj'cells(1,1)
- "A1"
- excel_obj'range("A1")
Here's the documentation for CreatePivotTable: https://docs.microsoft.com/en-us/office/vba/api/excel.pivotcache.createpivottable
I'm genuinely lost. Any insight would be amazing. Thanks!
-
This is a bit of a longshot, but in my experience working with excel, it is frequently looking for quote text. So, the first thing I would try would be let TABLE=TABLE_CACHE'CREATEPIVOTTABLE("""Chart!A1"""). Good luck.
-
The function expects a rangeobject, not a rangestring. Replace "Chart!A1" with *excel_obj'range("Chart!A1"):
let TABLE=TABLE_CACHE'CREATEPIVOTTABLE(*EXCEL_OBJ'RANGE("Chart!A1"))
-
The double quotes didn't work, but using:
TABLE=TABLE_CACHE'CREATEPIVOTTABLE(*EXCEL_OBJ'RANGE("Chart!A1"))
does something kind of odd?
It throws an error 88 (like everything else I've tried does), but instead of saying "Exception occurred (err/ret=2/0)" it says something new (to me):
"The server threw an exception (err/ret=2/0)"
If I try the same line 2 or 3 more times, it eventually works! It doesn't seem to be lag from creating the table_cache in the previous line. I sat waiting at the create pivot table line for a couple minutes once, and still had to retry it three times before it magically worked.
Very strange.
I'd bandage it with an err=*same but I don't think you can do that with LET statements...