Help with loading a macro into an excel sheet.

[wa4qms]

Good afternoon all.
I was wondering if anyone has been able to 'load' a macro file into an excel workbook and then have Progress run it?
Right now we are getting some xlsx files that need a bit of handling, and I wrote macros to handle this. But in order for
the user to be able to run them, they have to be stored in their 'personal' file. What is happening every time there is a
personal change the IT department has to copy the 'default' file into their desktop apps. I think this is a dead horse but
my boss asked me to look. Thanks!

 

[jdpjamesp]

I also think it's a dead horse. I think they should find a different solution. The MS trust centre stuff is going to make your life very hard indeed for this sort of thing. And it'll probably change tomorrow too.

 

[Rob Fitzpatrick]

Having business logic in VBA isn't where you want to be. Can you import and manipulate the data in ABL and then emit the required workbook artifact? That would save you having to deal with macro-enabled workbooks, which are a pain to manage and a vector for malware.

Right now we are getting some xlsx files

Or alternatively, see if you can receive the data in a different file format (CSV, JSON, whatever) so it's easier to work with programmatically.

 

[Osborne]

As James points out it is better if a different solution is found and the MS trust centre is going to make like difficult as you are likely to encounter a message which says "Programmatic access to Visual Basic Project is not trusted".

Rob makes some good points as if the files are in xlsx format they would need to be saved as xlsm.

But it can be done and if you have no choice and have to do this then a very simple example:

DEFINE VARIABLE chExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vWorkbook AS CHARACTER INITIAL "C:\General\Test.xlsx" NO-UNDO.

CREATE "Excel.Application" chExcel.

chExcel:Visible = TRUE.

// Open workbook
chWorkbook = chExcel:Workbooks:Open(vWorkbook).

// Import macro
chWorkbook:VBProject:VBComponents:Import("C:\Temp\Module1.bas").
// or
// chExcel:VBE:ActiveVBProject:VBComponents:Import("C:\Temp\Module1.bas").

// Run macro
chExcel:Run("ImportedMacro").

// Save as xlsm
vWorkbook = REPLACE(vWorkbook,".xlsx",".xlsm").
chWorkbook:SaveAs(vWorkbook,52,,,,,).

// Clean up
RELEASE OBJECT chWorkbook.
RELEASE OBJECT chExcel.