Run macro

[SkylineGTR]

Hi,

I have a macro in one workbook, that takes two parameters. For the
sake of clarity, something like this:

public sub TEST(pParam1 as string , pParam2 as string)
Debug.print pParam1 & "," & pParam2
end sub

In another workbook, I want to be able to:

1. Start a separate instance of Excel
2. Open the workbook.
3. Run TEST and pass in parameters

I thought that ShellExecute might do the trick, as it has a
"parameters" parameters - I thought this might be rather like the
SubAddress named parameters for Application.FollowHyperlink.
Incidentally, that works:

Application.FollowHyperlink
Address:="c:\FileToLoad.xls",SubAddress:="ThisWorkbook.TEST(""param1"",""param2"")"

but I want a new instance of Excel, which this does not do.

Any ideas/help anyone?

 

[Bob Phillips]

I think you need something like

Dim oXL As Object
Dim oWB As Object

Set oXL = CreateObject("Excel.Application")
With oXL
Set oWB = .Workbooks.Open("c:\FileToLoad.xls")
oXL.Run "FileToLoad2.xls!TEST", Param1, Param2
End With
oWB.Close savechanges:=False

Set oWB = Nothing
Set oXL = Nothing




--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

I also meant to ask why you feel you need a new instance of Excel, why not
just open the workbook in the current instance and run the macro from there?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[SkylineGTR]

Hi Bob

Thanks for the reply - much appreciated.

I wanted another instance because the workbook I am opening does things
to the user interface, whilst the one I am calling it from does not.
This way I could do what I need without affecting the caller workbook.
I want to be able also to switch between the two by using the taskbar
as well..

 

[Bob Phillips]

Did the first reply help?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[SkylineGTR]

Hi Bob,

Sorry for the delay in replying to you, I have been on holiday.

Your suggestion did not seem to work, Excel complained about not being
able to find the macro. The macro I was invoking was in ThisWorkbook,
declared as public with parameters. Any idea what I am doing wrong.

Code to invoke:

Private Sub CommandButton1_Click()
Dim oXL As Object
Dim oWB As Object
Set oXL = CreateObject("Excel.Application")
With oXL
Set oWB = .Workbooks.Open("c:\a.xls")
oXL.Run "a.xls!TEST", "a", "b"
End With
oWB.Close savechanges:=False
Set oWB = Nothing
Set oXL = Nothing
End Sub

Code being invoked (a.xls):

Public Sub TEST(p1 As String, p2 As String)
Debug.Print p1 & "," & p2
End Sub

in ThisWorkbook.

Cheers

 

[Dave Peterson]

Try moving the TEST subroutine to a General module.

Since you used Public, you could also use:

Option Explicit
Private Sub CommandButton1_Click()
Dim oXL As Object
Dim oWB As Object
Set oXL = CreateObject("Excel.Application")
With oXL
.Visible = True 'nice for testing
Set oWB = .Workbooks.Open("a.xls")
.Run "a.xls!thisworkbook.TEST", "a", "b"
End With
oWB.Close savechanges:=False
Set oWB = Nothing
'oXL.Quit
Set oXL = Nothing
End Sub

(specifying the module name, too)