PxPlus User Forum

Main Board => Discussions => Language => Topic started by: thenewguy on November 06, 2019, 03:41:18 PM

Title: Excel object commands issue
Post 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:

Code: [Select]
  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:

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!
Title: Re: Excel object commands issue
Post by: michaelgreer on November 06, 2019, 05:35:48 PM
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.
Title: Re: Excel object commands issue
Post by: koenv on November 07, 2019, 03:55:29 AM
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"))
Title: Re: Excel object commands issue
Post by: thenewguy on November 07, 2019, 08:39:55 AM
The double quotes didn't work, but using:

Code: [Select]
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...