Personal macros in Excel 1

[jjdavis]

There is a VB program that generates data and dumps it to an Excel spreadsheet. I have some macros that I would like to run after the data is generated. However, VB does not open Excel normally so the macros in my personal.xls workbook are not available.

Is there a way to get around this programmatically without having to open the personal.xls workbook?

The VB program is not my program, so I was trying to do this from the Excel side. If anyone knows of a way to do this in VB, I could relate that to the programmer.

Thanks,

 

[SmallCraig]

The method I use to get around this is to place the macro code in an external file; load the file contents as a macro at run-time; then execute the macro. This code comes from a VB program that has been doing this for 2 years (so I know it works):

    Set oExcel = New Excel.Application
    
    oExcel.Workbooks.Add
    strNewBookName = oExcel.ActiveWorkbook.Name
    
' pMacroFile is the name of the FILE containing the macro CODE for this process.
' After a new workbook is created, this code is inserted into "Module1" in the
' new workbook.
    With oExcel.ActiveWorkbook
        .Sheets.Add , , , xlModule
        .Sheets("Module1").InsertFile pMacroFile, True
        
        strNewSheetName = .ActiveSheet.Name
    End With

Then to run the macro:

        oExcel.Run pMacroName

Where "pMacroName" represents the name of the macro you want to run.

To create the external file, just copy the macro code from within the Personal Macro Workbook, and paste it into a new text file, with the name of your choice.

Of course, the VB project must have the Excel object model included as a reference (which it probably already does).

Hope this helps,

SmallCraig

 

[SBendBuckeye]

Hello SmallCraig,

Do I understand your post correctly that all of the above code would go into the current VB app that is referenced initially? Thanks for the clarification, I never thought of trying anything like that. Enjoy the star!

Have a great day!

j2consulting@yahoo.com

 

[SmallCraig]

Hi there SBendBuckeye,

Yes, you are correct - the code does go into the application. It's a bit fiddly to control another application from within VB, but it can be done!

And thanks for the star!

Regards,

SmallCraig
----------------------------------------
"Come see where justice and mercy collide
There on His hands and His feet and His side