PxPlus User Forum

Twitter Twitter Twitter

Author Topic: Excel object commands issue  (Read 1675 times)

thenewguy

  • Member
  • **
  • Posts: 15
    • View Profile
Excel object commands issue
« 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:
  • 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!
« Last Edit: November 07, 2019, 08:08:19 AM by thenewguy »

michaelgreer

  • Diamond Member
  • *****
  • Posts: 129
    • View Profile
Re: Excel object commands issue
« Reply #1 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.

koenv

  • Member
  • **
  • Posts: 12
    • View Profile
Re: Excel object commands issue
« Reply #2 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"))

thenewguy

  • Member
  • **
  • Posts: 15
    • View Profile
Re: Excel object commands issue
« Reply #3 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...