Excel object commands issue

[thenewguy]

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:

Code Select Expand

  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!

[Michael Greer]

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.

[Astecom - Koen]

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"))

[thenewguy]

The double quotes didn't work, but using:

Code Select Expand

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...