Can I pass control to a 2nd workbook
 

Apologies if this message is a near repeat of one I wrote earlier -
the earlier message woould appear to have been consigned to
hyperspace!

How can I, or indeed is it possible, to call a macro in a second
workbook and pass to it the value of a variable - in this case the
name of the 1st workbook?

The code that I have been trying to 'debunk' is:

Sub CallResultsAnalyser()

Dim ThisWb As String
Dim ResAnalysis As String

ThisWb = ActiveWorkbook.Name
ResAnalysis = "p:\results analyser.xls"

' Open the results analyser if it isn't already open
On Error Resume Next
Application.Workbooks.Open (ResAnalysis)
On Error GoTo 0

Application.Run ("'Results Analyser.xls'Main!AnalyseResults",ThisWb)

End Sub

It's the last line that is causing the problem: I am trying to run the
macro AnalyseResults(byval ThisWb as String) in the workbook Results
Analyser.xls ... can it be done? Is there a better way?

Thanks in anticipation ... even if the answer is bad news.

Alan

Can I pass control to a 2nd workbook
 

think you just have a typo in your run command

Application.Run ("'Results Analyser.xls'Main!AnalyseResults",ThisWb)


should be

Application.Run ("'Results Analyser.xls!Main.AnalyseResults",ThisWb)

This assume the macro AnalyseResults is located in a general/standard module
named "Main" in the Results Analyser.xls workbook and it is declared to take
a string argument.

Public Sub AnalyseResults( s as String)

--
Regards,
Tom Ogilvy


"Alan" wrote:

Apologies if this message is a near repeat of one I wrote earlier -
the earlier message woould appear to have been consigned to
hyperspace!

How can I, or indeed is it possible, to call a macro in a second
workbook and pass to it the value of a variable - in this case the
name of the 1st workbook?

The code that I have been trying to 'debunk' is:

Sub CallResultsAnalyser()

Dim ThisWb As String
Dim ResAnalysis As String

ThisWb = ActiveWorkbook.Name
ResAnalysis = "p:\results analyser.xls"

' Open the results analyser if it isn't already open
On Error Resume Next
Application.Workbooks.Open (ResAnalysis)
On Error GoTo 0

Application.Run ("'Results Analyser.xls'Main!AnalyseResults",ThisWb)

End Sub

It's the last line that is causing the problem: I am trying to run the
macro AnalyseResults(byval ThisWb as String) in the workbook Results
Analyser.xls ... can it be done? Is there a better way?

Thanks in anticipation ... even if the answer is bad news.

Alan

Can I pass control to a 2nd workbook
 

Check your other post, too.

Alan wrote:

Apologies if this message is a near repeat of one I wrote earlier -
the earlier message woould appear to have been consigned to
hyperspace!

How can I, or indeed is it possible, to call a macro in a second
workbook and pass to it the value of a variable - in this case the
name of the 1st workbook?

The code that I have been trying to 'debunk' is:

Sub CallResultsAnalyser()

Dim ThisWb As String
Dim ResAnalysis As String

ThisWb = ActiveWorkbook.Name
ResAnalysis = "p:\results analyser.xls"

' Open the results analyser if it isn't already open
On Error Resume Next
Application.Workbooks.Open (ResAnalysis)
On Error GoTo 0

Application.Run ("'Results Analyser.xls'Main!AnalyseResults",ThisWb)

End Sub

It's the last line that is causing the problem: I am trying to run the
macro AnalyseResults(byval ThisWb as String) in the workbook Results
Analyser.xls ... can it be done? Is there a better way?

Thanks in anticipation ... even if the answer is bad news.

Alan

--

Dave Peterson

Can I pass control to a 2nd workbook
 

Thanks guys. I had all but given up on the two messages that I had
posted: for whatever reason I couldn't see either of them on my
machine for the first 48 hours after posting!