Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Call Macro from another WorkBook?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default 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



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,290
Default 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-
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default 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-

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,290
Default 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-

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,758
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to call Macro(using ExecuteExcel4Macro) for a particular workbook [email protected] Excel Programming 6 December 22nd 04 11:23 AM
How can I call a macro in another workbook? ned Excel Programming 4 May 31st 04 09:50 PM
How do I change a macro to call a sheet from another workbook Rob van Gelder[_4_] Excel Programming 0 February 9th 04 08:30 AM
Open Another Workbook and Call Macro Bill Oertell[_2_] Excel Programming 1 January 20th 04 04:29 AM
Call a macro in other workbook Davwe[_4_] Excel Programming 2 December 17th 03 08:14 PM


All times are GMT +1. The time now is 09:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement