1. Sylvain .
  2. PowerBuilder
  3. Tuesday, 10 March 2020 16:10 PM UTC
I'm trying to create a group inside an excel sheet from PB script,
I have found the instruction to do so at this address:

The VBA instruction look like this:
   Worksheets("Sheet1").Activate.Selection.Subtotal("GroupBy:=1, Function:=xlCount, TotalList:=Array(1), Replace:False, PageBreaks:False, SummaryBelowData:False")
My PB Script:
  any la_Array[1]
      this.Application.ActiveWorkbook.ActiveSheet.Range("A2").Subtotal(1, -4112, la_Array, False, False, False)
When I tried to execute that code I received an 'Error calling external object function subtotal at line...'
my problem reside in the VBA instruction  Array(1)  from the VBA script,
Anyone know how to translate the Array(1) instruction to PB script.
Thank you for your time and help
Sylvain . Accepted Answer Pending Moderation
  1. Tuesday, 10 March 2020 17:36 PM UTC
  2. PowerBuilder
  3. # 1

Thank you Miguel,

You gave me an idea and it work:

from the PB code 

  this.Application.ActiveWorkbook.ActiveSheet.Range("A2").Subtotal(1, -4112, la_Array, False, False, False)

I have replace the la_Array like this:

  this.Application.ActiveWorkbook.ActiveSheet.Range("A2").Subtotal(1, -4112, this.Application.ActiveWorkbook.ActiveSheet.Range("A2").Offset(0,0).select, False, False, False)


Essentially I ask Excel using the 'Offset' instruction to give back the array needed by the subtotal function.

Also for your knowledge you can use  'any la_Array[]'  when you want to set value to columns like this:

any la_Array[4]

   la_Array[1] = "Customer ID"

   la_Array[2] = "Invoice ID"

   la_Array[3] = "Trx Date"

   la_Array[4] = "Amount"

this.Applicatin.ActiveWorkbook.ActiveSheet.Range("A1:D1").Value = la_Array


Thank you again for your help it was appreciated.



  1. Armeen Mazda @Appeon
  2. Tuesday, 10 March 2020 18:22 PM UTC
Thanks for sharing the solution!
  1. Helpful
  1. Miguel Leeuwe
  2. Wednesday, 11 March 2020 00:47 AM UTC
Well glad I could help, event though I'm not sure how :)

Thanks for sharing your information, that's great info for anyone else!

  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 10 March 2020 16:32 PM UTC
  2. PowerBuilder
  3. # 2

I don't think you can pass an ANY datatype to Excel, maybe you first have to put whatever is in that Array(1) of your Excel sheet into an OLE object and pass that ole object instead.

(haven't tried this myself)

There are no comments made yet.
  • Page :
  • 1

There are no replies made for this question yet.
However, you are not allowed to reply to this question.