running an excel macro

[drew10]

Does anyone know how to programmatically fire an excel macro from a vb.net app?
Thanks,
Drew

 

[drew10]

I found an answer to my own question, but it presents another problem. Here is my code:

        Dim oExcel As Excel.Application
        Dim oBook As Excel.Workbook

        oExcel = New Excel.Application()
        oBook = oExcel.Workbooks.Open(fileName)
        oBook.Application.Run("runMacros")
        oBook.Save()
        oExcel = Nothing
        oBook.Close()
        oBook = Nothing

I have a loop that runs the macro on several files. It causes quite a memory leak for each file opened. Does anyone know how to stop this?

 

[drew10]

I found a solution to my problem. It might prove useful to someone else, so here it is. You can force the garbage collector routine with:

GC.Collect()

It closes any applications the program is no longer using.

 

[Kris11]

oBook.Application.Run("runMacros&quot

In this example what is "runMacros"?, In my case I have a Macro file which I want to execute on an Excel file and when I substitute "runMacros" with the physical file location as an example "C:\temp\Macro.mac" it gives me an error message that macro file no found? Can any one help me to understand how can I run a excel macro on a file.

 

[Zarcom]

Should be able to open that file in excel, run the macro, save the file, then close the file. That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[Kris11]

Nope, It didn't work..... the problem here is I don't have Macros within the Excel file, it is a seperate file. What I am doing is create an excel file depending on some data retrieval and then format it with a macro file which I have already created using Macro editor, like I need to run "Macro.mac" (this is my macro file) on mydata.xls. Everything works fine except running this macro on Excel file. Any suggestions or idea??

Thanks

 

[drew10]

When I wrote this app, all that I needed to do was open an existing excel spreadsheet and fire a macro that was already created within the spreadsheet. I called oBook.Application.Run("runMacros&quot which is the macro name for a macro that fires many smaller macros. I would explore methods of adding the macro to the excel spreadsheet, and then running the macro from within the spreadsheet... but that's just a thought.

hth,
drew10

 

[Kris11]

I figured out a way to do this but I am not 100% satisfied with the solution. What I did was

1. I started inserting retrieved values to the file that has macro in it. In this case "Macro.mac"

2. Then I was able to run the macro as below

oBook.Application.Run("runMacros&quot

3. And after formatting do a SaveAs on the "oBook" to the file name I want.

It worked fine but the issue is that each saved "xls" file will have all my macros in it, in other words I am exposing/distributing all my Macros to all my users. Still I am investigating on options to avoid exposing my macro to all users.

 

[Kris11]

Hi All,

Just in case anybody is interested. I found a way to remove the macro which was added to the excel.

With excelBook.VBProject
.VBComponents.Remove(.VBComponents.Item("module1&quot)
End With

Here "module1" is the name of the macro which I have in the Excel sheet. So what I do now is

1. I started inserting retrieved values to the file that has macro in it. In this case "Macro.mac"

2. Then I was able to run the macro as below

oBook.Application.Run("runMacros&quot

2a. Remove the whole module from the excel which will remove the macros in the module as well

With excelBook.VBProject
.VBComponents.Remove(.VBComponents.Item("runMacros&quot)
End With

3. And after formatting do a SaveAs on the "oBook" to the file name I want