Macro Error(??)

[Guest]

Hi. I'm trying to run an excel macro through a VB app and am getting 2 weird errors (one which I managed to fix but am not sure why it was broken in the first place). Here's the little snippet of code causing the problems:

Dim XLapp As Excel.Application
Set XLapp = New Excel.Application
Dim WB As Excel.Workbook
Set WB = XLapp.Workbooks.Open(filepath_here)
XLapp.Run "macro_name", file_i_want_macro_to_act_on

The first weird bug is, the XLapp.Workbooks.Open fails if I give
an absolute filepath. I fixed it by giving a relative path, but I'm not
sure why that is necessary.

The second problem I still cannot resolve is I get an error saying that
a macro with that name could not be found (but there is a macro with
that name). Am I calling the Run method improperly??

Thanks.

 

[Guest]

I just wanted to be more explicit since maybe there's more to macro names
than I know (?)

Say within a file named foo.xls, I have a macro named my_macro.
Then I typed the following:

Dim XLapp As Excel.Application
Set XLapp = New Excel.Application
Dim WB As Excel.Workbook
Set WB = XLapp.Workbooks.Open("foo.xls")
XLapp.Run "my_macro", file_i_want_macro_to_act_on

and if it matters, foo.xls has many other macros too but i specifically
want to call my_macro.

 

[Guest]

Hm, well I managed to fix problem #2 heh. And apparently problem #1
was because I had the absolute path in quotes. As soon as I got
rid of those it started working (although I still think it's strange since
quotes around file path names i thought was syntactically correct..and
sometimes even necessary when the filepath has spaces).

Well, if anyone out there was having the same problem as me, here is how
I fixed it:

Dim XLapp As Excel.Application
Set XLapp = New Excel.Application
Dim WB As Excel.Workbook
Dim WB2 As Excel.Workbook
Set WB = XLapp.Workbooks.Open(foo.xls) ' file that has the macro that i want to execute
Set WB2 = XLapp.Workbooks.Open(file.xls) ' xls file that i want the macro to operate on
XLapp.Run "foo.xls!" & macro_name ' Note the exclamation point after foo.xls

' After everything is over let's clean up
WB.Close False
WB2.Close False
XLapp.Quit
Set WB = Nothing
Set WB2 = Nothing
Set XLapp = Nothing