Call Macro from another WorkBook?

[Steven M. Britton]

I am trying this:

Sub MYTD_Summary()
Application.Run "\\arlfs03\shared\Engineering\ENG\BOM Archive\BOM
Macros.xls!MYTD_Summary"
End Sub

But I get a Run-time error '1004'

The macro '\\arlfs03\shared\Engineering\ENG\BOM Archive\BOM
Macros.xls!MYTD_Summary' cannot be found.

If I assign the macro to the object it works fine - It's an oval I drew
using the drawing tools in Excel, not a command button.

What am I missing?

This is the code from the BOM Marcos.xls File:

Sub MYTD_SUMMARY()
Range("AZ6").Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(-1, 0).Select: ENDmonth = ActiveCell.Column - 2::
ActiveCell.Offset(0, 1).Select

ActiveCell.Offset(0, -1).Select: Period = ActiveCell.Value
If Left(Period, 4) = "C/O " Then
CHANGEmonth = ActiveCell.Column - 2
end if
If ActiveCell.Column = 2 Then CHANGEmonth = 0
Cells.Find(What:="DASHBOARD SNAPSHOT", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:= _
xlNext, MatchCase:=False).Activate
ActiveCell.Offset(5, 1).Select
myCOL = ActiveCell.Column
myRow = ActiveCell.Row
ActiveWorkbook.Names.Add Name:="SUMdata", RefersToR1C1:= _
"=Summary!R" & myRow & "C" & myCOL & ":R" & (myRow + 14) & "C" &
(ENDmonth + 2)
Application.GoTo Reference:="QTRreport"
ActiveCell.Offset(3, 3).Select: ActiveCell.Value = CHANGEmonth
ActiveCell.Offset(0, 1).Select: ActiveCell.Value = ENDmonth
ActiveCell.Offset(2, -2).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
End If
Beep

End Sub

 

[Gary's Student]

Try:

Windows("\\arlfs03\shared\Engineering\ENG\BOM Archive\BOM
Macros.xls").Activate

Before the Application.Run. Even if it fails, it will indicate if VBA is
having trouble with the networked spreadsheet or the macro within the
spreadsheet

 

[Jim Cone]

Steven,

Is the macro code located in the module behind the sheet?
Code in a sheet module is "private" by default.
If so, the module name must be specified, (or move the code
to a general module).

....BOMMacros.xls!Sheet1.MYTD_Summary

Jim Cone
San Francisco, USA



message I am trying this:

Sub MYTD_Summary()
Application.Run "\\arlfs03\shared\Engineering\ENG\BOM Archive\BOM
Macros.xls!MYTD_Summary"
End Sub

But I get a Run-time error '1004'

The macro '\\arlfs03\shared\Engineering\ENG\BOM Archive\BOM
Macros.xls!MYTD_Summary' cannot be found.

If I assign the macro to the object it works fine - It's an oval I drew
using the drawing tools in Excel, not a command button.

What am I missing?
-snip-

 

[Steven M. Britton]

Jim,

Thanks for the reponse, no it's not behind a sheet. Although you made me
think, this is a Macro that has several subs. The Macro is called
Lifecycle_Processing and in that Macro I am trying to call the MYTD_Summary
sub routine. Does that have to be called differently?

It's strange cuz it will open the file BOM_Macros in a new Excel window, but
then gives me the error...

 

[Jim Cone]

Steven,

If the workbook is shared, then Excel handles things a little differently
and I can't help you with that.
If the new window is in a new instance of Excel, then there will also
be problems that I can't help you with.

If is not a shared workbook, I would try two separate steps...
first open the BOM_Macros workbook (if is not already open)
then call the MYTD_Summary sub.

Regards,
Jim Cone
San Francisco, USA



Jim,

Thanks for the reponse, no it's not behind a sheet. Although you made me
think, this is a Macro that has several subs. The Macro is called
Lifecycle_Processing and in that Macro I am trying to call the MYTD_Summary
sub routine. Does that have to be called differently?

It's strange cuz it will open the file BOM_Macros in a new Excel window, but
then gives me the error...

 

[Dave Peterson]

How about:

Application.Run "BOM Macros.xls!MYTD_Summary"
or maybe...
Application.Run "'BOM Macros.xls'!MYTD_Summary"

You don't need the path, since the workbook containing the macro has to be open.

But you may need the single quotes (since your workbook's name has that embedded
space).

 

[Steven M. Britton]

This is what I got to make it work...

Workfile = ActiveWorkbook.Name
Workbooks.Open Filename:= _
"\\arlfs03\shared\Engineering\ENG\BOM Archive\BOM Macros.xls"
Windows(Workfile).Activate
Application.Run "'BOM Macros.xls'!MYTD_Summary"

Workbooks("BOM Macros.xls").Close SaveChanges:=False