Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro
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:="ThisWork book.TEST(""param1"",""param2"")" but I want a new instance of Excel, which this does not do. Any ideas/help anyone? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro
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) "SkylineGTR" wrote in message ups.com... 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:="ThisWork book.TEST(""param1"",""par am2"")" but I want a new instance of Excel, which this does not do. Any ideas/help anyone? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro
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" wrote in message ups.com... 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:="ThisWork book.TEST(""param1"",""par am2"")" but I want a new instance of Excel, which this does not do. Any ideas/help anyone? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro
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.. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro
Did the first reply help?
-- HTH RP (remove nothere from the email address if mailing direct) "SkylineGTR" wrote in message oups.com... 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.. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro
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) SkylineGTR wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |