ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Call Macro from another WorkBook? (https://www.excelbanter.com/excel-programming/331347-call-macro-another-workbook.html)

Steven M. Britton[_2_]
Call Macro from another WorkBook?
 
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
Call Macro from another WorkBook?
 
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

--
Gary's Student


"Steven M. Britton" wrote:

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

Jim Cone
Call Macro from another WorkBook?
 
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



"Steven M. Britton" wrote in
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[_2_]
Call Macro from another WorkBook?
 
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" wrote:

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



"Steven M. Britton" wrote in
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-

Jim Cone
Call Macro from another WorkBook?
 
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



"Steven M. Britton" wrote in message
...
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" wrote:

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



"Steven M. Britton" wrote in
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-

Dave Peterson[_5_]
Call Macro from another WorkBook?
 
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 wrote:

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

--

Dave Peterson

Steven M. Britton[_2_]
Call Macro from another WorkBook?
 
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


"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


All times are GMT +1. The time now is 01:40 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com