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

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

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

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!