1. Konrad Kaltenbach
  2. PowerBuilder
  3. Monday, 10 May 2021 09:18 AM UTC

Dear Experts,

I want to call VisualBasic for Application (VBA) macros and functions in Word or Excel documents which I have written myself out of/from PowerScript functions (instead of rewritting them in the PowerScript functions).

Is this possible?
If it is: How is the syntax?

Assume, I have an Excel file with a macro Macro1()  and a function Fct1(ByVal Something As String) As Integer  in Module1 of its VBAProject and a PowerScript function containing the following lines:

"String          ls_FileWithMacro
OLEObject     xlsInstanz

ls_FileWithMacro = ... // Full name of the Excel file

xlsInstanz = CREATE OLEObject
xlsInstanz.connecttoobject( ls_FileWithMacro )"

How could I call Macro1 and/or Fct1 afterwards?


Thanks in advance for all trials to help me.
Kind regards,
Konrad Kaltenbach

Accepted Answer
Konrad Kaltenbach Accepted Answer Pending Moderation
  1. Monday, 10 May 2021 14:44 PM UTC
  2. PowerBuilder
  3. # Permalink

Before calling functions and subroutines (like SaveAs e. g.) provided by VBA you have to activate the window with the office application first*. And if you want to call your own stuff this is sufficient, too:

lole_Win = xlsInstanz.Application.Windows(ls_nameOfExcelFile)

lole_Win.Activate

// Now the following calls are successful without exception:

xlsInstanz.Application.Run("Makro1") //
xlsInstanz.application.run("Modul1.Makro1") //
xlsInstanz.application.run("Makro3", "line1: Greetings from", "line2: PowerScript") //
xlsInstanz.application.run("Modul1.Makro3", "line1", "line2") //
ll = long(xlsInstanz.application.run("Makro2", "line1: Waiting for", "line2: an integer")) //
messagebox("ll: " , String(ll))


*https://community.appeon.com/index.php/qna/q-a/how-calling-vba-excel-functions-with-optional-arguments

Comment
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Monday, 10 May 2021 09:44 AM UTC
  2. PowerBuilder
  3. # 1

Hi Konrad,

you can call the Run method of the Application object:

https://docs.microsoft.com/office/vba/api/excel.application.run

HTH,

René

 

Comment
  1. Konrad Kaltenbach
  2. Monday, 10 May 2021 12:22 PM UTC
Hi René,

thanks for your answer.

Application.Run did work sometimes but now it doesn't anymore. I don't know why. In detail:

For testing Application.Run I defined the following in an Excel document:



Sub Makro1()

MsgBox ("Message defined" & vbCrLf & "within VBA.")

End Sub



Function Makro2(ByVal mess As String, _

ByVal mess2 As String) As Integer

MsgBox (mess & vbCrLf & mess2)

Makro2 = 1

End Function



Sub CallMakro2()

Call Makro2("vb interner", "Aufruf")

End Sub



Sub Makro3(ByVal mess As Variant, _

ByVal mess2 As Variant)

MsgBox (mess & vbCrLf & mess2)

End Sub.



In my PowerScript function I tried the following calls:

Long ll

xlsInstanz.application.run("Modul1.Makro1") // first it worked, now it doesn't anymore.

xlsInstanz.application.run("Makro3", "line1: Greetings from", "line2: PowerScript") // worked occasionally

xlsInstanz.application.run("Modul1.Makro3", "line1", "line2") // never worked

ll = long(xlsInstanz.application.run("Makro2", "line1: Waiting for", "line2: an integer")) // worked one time (?)



I'm at a loss.

  1. Helpful
  1. René Ullrich
  2. Monday, 10 May 2021 13:15 PM UTC
At moment I don't know why this happens.

Try to catch the OLERuntimeError and see the Description property to get more information about the error.

I guess that the macro is not available on secend call. But I don't know why.
  1. Helpful
There are no comments made yet.
Matt Balent Accepted Answer Pending Moderation
  1. Monday, 10 May 2021 13:26 PM UTC
  2. PowerBuilder
  3. # 2

I havn't done this in a while but once you have connected to the ole object you should be able to call the method.

 

xlsInstanz = CREATE OLEObject
xlsInstanz.connecttoobject( ls_FileWithMacro )

Assuming you have connected you can then

xlsInstanz.nameofmacro()

How would you call it in VBA?

Comment
  1. Konrad Kaltenbach
  2. Monday, 10 May 2021 14:22 PM UTC
xlsInstanz.nameofmacro() doesn't cause an error or throws an exception but is just ignored.

In VBA the following works:



Sub CallMakro2()

'VBA Excel macro

Call Makro1 ' works

Makro1 ' works

Call Makro2("VB internal", "call") ' works

End Sub



Thanks for your response.

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