Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Excel VBA from PB script

  1. How-to
0
Votes
Undo
  1. Sylvain .
    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:

   https://docs.microsoft.com/en-us/office/vba/api/excel.range.subtotal
 
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
 
Debug with Visual Studio and PowerBuilder for C# c...
Search & Replace dwo editor bug in 4k
Responses (2)
Sylvain .
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.

Sylvain

 

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Tuesday, 10 March 2020 18:22 PM UTC
Thanks for sharing the solution!
  1. Helpful
Miguel Leeuwe
  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!

regards
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
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)

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