PDA

View Full Version : Solved: Help with syntax to Run code in one XLS from another XLS



akinator
07-19-2005, 11:50 AM
Hi folks - I'm new to VBA - so please forgive me if this is simple. I've searched the forums to no avail.
I have 2 xls fils. File1 has cmdbutton1 in it on sheet#4 that runs some code. I would like to run file1's cmdbutton1 from File2.
I am able to open the file, set the active worksheet, but can't seem to get the syntatx to work to actually run the code.
What I have so far is:

Worksheet containing the button with the code: "Sheet4"
Button with code: "Sched1"
The code is contained in the click proc "Sched1_Click"



Private Sub CommandButton1_Click()
'Macro purpose: Use the application.run method to execute
'a macro without arguments from another workbook

Dim PathToFile As String, _
NameOfFile As String, _
wbTarget As Workbook, _
CloseIt As Boolean

'Set file name and location. You will need to update this info!
NameOfFile = "XX_Test.xls"
PathToFile = "C:\mydir\subdir"

'Attempt to set the target workbook to a variable. If an error is
'generated, then the workbook is not open, so open it
On Error Resume Next
Set wbTarget = Workbooks(NameOfFile)

If Err.Number <> 0 Then
'Open the workbook
Err.Clear
Set wbTarget = Workbooks.Open(PathToFile & "\" & NameOfFile)
CloseIt = True
End If

'Check and make sure workbook was opened
If Err.Number = 1004 Then
MsgBox "Sorry, but the file you specified does not exist!" _
& vbNewLine & PathToFile & "\" & NameOfFile
Exit Sub
End If
On Error GoTo 0

Worksheets("Sheet4").Activate

'------------------------
'CANT GET THIS TO WORK
Sched1_Click()
'------------------------


If CloseIt = True Then
'If the target workbook was opened by the macro, close it
wbTarget.Close savechanges:=False
Else
'If the target workbook was already open, reactivate this workbook
ThisWorkbook.Activate
End If

END Sub


Thank you so much!

Bob Phillips
07-19-2005, 12:16 PM
Hi folks - I'm new to VBA - so please forgive me if this is simple. I've searched the forums to no avail.
I have 2 xls fils. File1 has cmdbutton1 in it on sheet#4 that runs some code. I would like to run file1's cmdbutton1 from File2.
I am able to open the file, set the active worksheet, but can't seem to get the syntatx to work to actually run the code.

For a new guy, you have picked a tricky start topic. :)

You cannot access a class in another workbook, at least not, directly. You have to get at it in a roundabout way. A worksheet is just a special instance of a class, so it applies to that also.

In the workbook with the class you have to create a a standard code module procedure for doing the business with the class.

Public Sub TestMyClass()
Sheet1.CommandButton1_Click
End Sub

where Sheet1 is the sheet with the commandbutton on.

Note that it is a Public sub. You will also need to make the c ommandbutton event code public, as it will be called directly.

Then in the calling workbook, from the commandbutton click event, you invoke that routine like so

Private Sub CommandButton1_Click()
MsgBox "Workbook 2 calling"
Application.Run "Book1!TestMyClass"
End Sub


where Book1 is the name of the other workbook.

akinator
07-19-2005, 02:22 PM
Wow - thanks so much for the quick response. At first i couldn't get it to work trying just the code supplied. It did work, however if the xls file is opened first (Workbooks.Open). Is it necessary to open the workbook first for the application. run command to work? In any case so far so good - thanks a million!

Bob Phillips
07-19-2005, 02:57 PM
Wow - thanks so much for the quick response. At first i couldn't get it to work trying just the code supplied. It did work, however if the xls file is opened first (Workbooks.Open). Is it necessary to open the workbook first for the application. run command to work? In any case so far so good - thanks a million!

Yeah it does. Sorry I should have mentioned it.

akinator
07-19-2005, 03:38 PM
Now, for another dumb question. How do I get this message listed as Solved (it seems that's what's done in here)? Once again, thanks for your help.

xCav8r
07-19-2005, 03:44 PM
At the top of the window for this thread, you should see a link called "Thread tools". that's what you're looking for.

akinator
07-20-2005, 05:09 AM
Thanks!