How can I programmatically create Excel Macro/VBA Code ?

[Chris Kelleher]

Using Progress COM calls I am creating a brand new Excel Workbook
from a Text file. I now want to add Workbook_Activate() and
Workbook_Deactivate() trigger code. This is in order to add/delete a Toolbar
so that it only appears when the user is in one of my application's
Workbooks.

Has anyone found a way to do this programmatically ?

I can't find a Collection of Macros that I could do an ADD method to
or an object called VBAProject with a collection of triggers.


Mick Hand
Senior Technical Consultant
OpenAccounts Ltd
Mick@openaccounts.com <mailto:Mick@openaccounts.com>

 

[Chris Kelleher]

I have now found the answer myself.
I found the VBE property and that led to the Object map of the VB
Editor and from there the answer was easy (well not exactly easy).

The following program seems to work fine and creates two Event
Procedures in the VBA Project of my Excel Workbook.

def var chExcelApp as com-handle no-undo.
def var chWorkBook as com-handle no-undo.
def var chWorkSheet as com-handle no-undo.
def var chTemp as com-handle no-undo.
def var chVBE as com-handle.
def var chVBEWindow as com-handle.
def var chVBAProject as com-handle.
def var chVBComponent as com-handle.
def var chCodeModule as com-handle.

def var i as int no-undo.
def var iLine as int no-undo.

create "Excel.Application" chExcelApp.

chExcelApp:Visible = yes.


chExcelApp:Workbooks:OpenText("c:\work\coresrc\fred.txt",2,,,,,True).

assign
chWorkBook = chExcelApp:WorkBooks:Item(1)
chWorkSheet = chExcelApp:Sheets:Item(1).

chVBE = chExcelApp:VBE.
chVBAProject = chWorkbook:VBProject.

chVBComponent = chVBAProject:VBComponents:Item(1). /* ThisWorkbook
*/
chCodeModule = chVBComponent:CodeModule.

iLine = chCodeModule:CreateEventProc("Activate","Workbook") + 1.
chCodeModule:InsertLines(iLine,'
Application.CommandBars("OpenAccounts").Visible = True').

iLine = chCodeModule:CreateEventProc("Deactivate","Workbook") + 1.
chCodeModule:InsertLines(iLine,'
Application.CommandBars("OpenAccounts").Visible = False').

chVBEWindow = chVBE:MainWindow.

release object chVBAProject no-error.
release object chVBComponent no-error.
release object chCodeModule no-error.
release object chVBE no-error.

chVBEWindow:Visible = False.
release object chVBEWindow no-error.

chExcelApp:CommandBars("OpenAccounts"):Visible = True.

/* release everything except Excel itself */
release object chTemp no-error.
release object chWorkSheet no-error.
release object chWorkBook no-error.

/* quit Excel if running in Background */
if session:batch-mode then chExcelApp:Quit().

/* release Excel */
release object chExcelApp no-error.


Mick Hand
Senior Technical Consultant
OpenAccounts Ltd
Mick@openaccounts.com <mailto:Mick@openaccounts.com>