Macros, run an Excel macro from 4GL

[wa4qms]

Greeting all! I am trying to determine if there is a method to call a stored macro (excel) from a program? From what I have read, I could use the code from the macro and embed it into the .p / .w, but some of the sheets I work with will change and it is just easier to modify the macro then try and put it into the code. (Long story on the reason why.) Maybe emulate key-strokes? I open for suggestions and thanks for readying. -Dennis-

 

[Osborne]

It is certainly possible to run an Excel macro from 4GL, and as a quick test created a simple macro in an Excel file which was the following:

Sub Macro1()
    MsgBox "Hello World."
End Sub

Running this code in 4GL gave the message:

DEFINE VARIABLE hExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE hWorkbook AS COM-HANDLE NO-UNDO.

CREATE "Excel.Application" hExcel.

hWorkbook = hExcel:Workbooks:Open("C:\Temp\RunMacro.xlsm").
hExcel:Visible = TRUE.
hExcel:Run("Macro1").

IF VALID-HANDLE(hWorkbook) THEN
   RELEASE OBJECT hWorkbook.
IF VALID-HANDLE(hExcel) THEN
   RELEASE OBJECT hExcel.

 

[Rob Fitzpatrick]

Good way to perform code injection in an application.

 

[wa4qms]

The problem I am now having is that the macro I want to run is stored in personal.xlsb, and if I use the standard progress code, it is not pulling in that file. If there is no way to have progress load that info, the ability to run a macro becomes extremely limited. In as much as this test macro if somewhat simple, I think I might be able drag the commands into application code, but I have a macro in my system that calls 6 other macros, and some are extremely complicated. That's a road I'd rather not go down.
-Dennis-

 

[Osborne]

You can certainly run macros contained in other files using something similar to this:

hExcel:Run("'Another Workbook.xlsm'!NameOfMacro").

As for loading info from another workbook that could be possible and think I did something similar many years ago but it is so long ago just cannot find it. All I could find was some old VBA workings for exporting and importing macros between workbooks:

Sub CopyModule(SourceWB As Workbook, strModuleName As String, TargetWB As Workbook)
' requires a reference to the Microsoft Visual Basic Extensibility library
' copies a module from one workbook to another
' example: CopyModule Workbooks("Book1.xls"), "Module1", Workbooks("Book2.xls")
    Dim strFolder As String, strTempFile As String

    strFolder = SourceWB.Path
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & ""
    strTempFile = strFolder & "~tmpexport.bas"
    On Error Resume Next
    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
    TargetWB.VBProject.VBComponents.Import strTempFile
    Kill strTempFile
    On Error GoTo 0
End Sub

However, what this shows is the key in Progress is to access VBProject:VBComponents, and this simple example shows how to auto create a simple macro in Progress:

DEFINE VARIABLE chExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chModule AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorksheet AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE macroCode AS CHARACTER NO-UNDO.

CREATE "Excel.Application" chExcel.

chWorkbook = chExcel:Workbooks:Add().
chWorkSheet = chExcel:Sheets:Item(1).

/* Add a module to the workbook */
chModule = chWorkbook:VBProject:VBComponents:Add(1).

/* Create the macro code */
macroCode = "Sub Macro1()" + CHR(13) + CHR(9) + "MsgBox " + CHR(34) + "Hello World." + CHR(34)
          + ", vbInformation, " + QUOTER("Information") + CHR(13) + "End Sub".

/* Add the macro code */
chModule:CodeModule:AddFromString(macroCode).

chWorkbook:SaveAs("C:\Temp\MacroAdded.xlsm",52,,,,,).

chExcel:Visible = TRUE.
chExcel:Run("Macro1").

IF VALID-HANDLE(chModule) THEN
   RELEASE OBJECT chModule.
IF VALID-HANDLE(chWorksheet) THEN
   RELEASE OBJECT chWorksheet.
IF VALID-HANDLE(chWorkbook) THEN
   RELEASE OBJECT chWorkbook.
IF VALID-HANDLE(chExcel) THEN
   RELEASE OBJECT chExcel.