Call a macro in an opened excel file

[mh1349]

So i want to be able to start an excel macro in a file that is already opened by a user. Like a user would open the excel file and look at it, but when there is new data, my vb.net program will run, call out the macro in the opened file and update the excel file in front of the user. Is this something that is possible?

Every time I try to get this done it runs the macro of the opened file but doesn't update it. Instead it saves it over the excel file, so you have to close the excel file and reopen it to see the new data. But I don't want to have to do that.

Here is my code used for this instance:

Dim xl As New Excel.Application
Dim oTargetWb As Excel.Workbook
oTargetWb = xl.Workbooks.Open(fileTest)
Try
Dim F As Short = FreeFile()
FileOpen(F, fileTest, OpenMode.Binary, OpenAccess.ReadWrite, OpenShare.LockReadWrite)
FileClose(F)
Catch
xl.Run("test2")
End Try


Any suggestions?

[danutzdobre]

Hello,

I am using something similar but it opens the file in a new instance of excel and runs the macro there. Once it finishes i have 2 files open.

Also i cannot run a macro from other workbook and i guess it's related to this as well.

i am using

Dim oXL As New Excel.Application
        Dim oWB As Excel.Workbook
        'Dim oRng As Excel.Range
        oXL = New Excel.Application
        oXL.Visible = True
        oWB = oXL.Workbooks.Open("C:\Users\DealExport.xlsx")
 
 
        oXL.Run("RunMeee")
      
        oXL = Nothing
        oWB = Nothing

If the macro is in a different file i get an error it is nonexistent even if i try with this:

oXL.Run("C:\Users\xxxxx\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB!RunMeee")

So some help would be nice to run it on an already open workbook.

Sorry to post here but i think it's related.

Thank you.

[dunfiddlin]

So i want to be able to start an excel macro in a file that is already opened by a user. Like a user would open the excel file and look at it, but when there is new data, my vb.net program will run, call out the macro in the opened file and update the excel file in front of the user. Is this something that is possible?

Which file format are you using?

[dunfiddlin]

Hello,

I am using something similar but it opens the file in a new instance of excel and runs the macro there. Once it finishes i have 2 files open.

Also i cannot run a macro from other workbook and i guess it's related to this as well.

As .xlsx is the macro-free format is it possible that the second application is being run to give you a version in which you could save the macro as part of the file?

[mh1349]

I am using .xlsm if that's what you meant

[dunfiddlin]

It is! I can have a little play now!

[danutzdobre]

As .xlsx is the macro-free format is it possible that the second application is being run to give you a version in which you could save the macro as part of the file?

I edited the code a little bit. the file i am using is .xlsb
The original one is .xlsx and i need to run the macro from another file like PERSONAL.XLSB.

To solve mh1349's problem we need to replace the "oTargetWb = xl.Workbooks.Open(fileTest)" with something else i guess, to just activate an open window instead of opening it again and saving it after running.

Is there a way to just run a macro without having to open a file and then save it? Just open like it would run directly from excel.

Danut

[dunfiddlin]

I am using .xlsm if that's what you meant

Ok, I've run a few tests and I'm afraid I'm just not seeing what you are. The macros run and update without problems. So unless there's something specific to the macro you're running I don't know what to suggest.

[dunfiddlin]

Is there a way to just run a macro without having to open a file and then save it? Just open like it would run directly from excel.

I'm not sure I really understand the question in that form but it's certainly possible to have the Excel Application persist simply by declaring it with form or global scope. So unless you actually need two workbooks open (and if you're going to use a macro from one in the other you probably do) you can certainly do everything in one application instance. As I've suggested in my latest answer, running macros doesn't require saving and reopening and should update in the present instance.

[mh1349]

Ok, I've run a few tests and I'm afraid I'm just not seeing what you are. The macros run and update without problems. So unless there's something specific to the macro you're running I don't know what to suggest.

Everything works fine when the excel file that has the macro in it is closed. But when I have it opened, it runs the macro in the background and doesn't update the file I have opened. Are you saying you had an excel file open then ran the vb program and it worked?

[dunfiddlin]

I am. Sorry.

[mh1349]

So it did work for you while you had the file open?

[danutzdobre]

Isn't this line oWB = oXL.Workbooks.Open("C:\Users\DealExport.xlsb") opening the file? Is there any way i can run the macro without this open command?

[mh1349]

Dim fileTest As String = "N:\Temp\template.xlsm"

Try
Dim F As Short = FreeFile()
FileOpen(F, fileTest, OpenMode.Binary, OpenAccess.ReadWrite, OpenShare.LockReadWrite)
FileClose(F)
Catch
Dim xl As Excel.Application = Marshal.GetActiveObject("Excel.Application")
xl.Visible = True
xl.Run("test2")
Exit Sub
End Try

This is what finally worked for me. Because it finds the currently run excel object (being my already opened excel file) then runs its macro.

[danutzdobre]

Glad it worked for you

For me is not working yet.
I have the excel macro in a file "Macro.xlsb" and i want to run it in file "DealExport.xlsx".

I open DealExport.xlsx and then i need to run the macro. How do i do that? Even if i have both files open it does not work.

[danutzdobre]

Any help here? Do i need to start a new thread?