Running a Macro in one workbook from another workbook

[Saladsamurai]

Okay then Quick overview. I am writing a quick and dirty script to run some stuff for me over the weekend.

I have this workbook called 20LW6_TEST_redo.xls. 20LW6_TEST_redo.xls has a command button on it which, when clicked, will call another program from the shell.

In 20LW6_TEST_redo.xls, the Private sub that runs when the command button (located on Sheet4) is clicked is the following:

Private Sub CommandButton1_Click()
    Call GetOverallData
    Advanced.Show
End Sub

I thought that this would be a simple matter of

1) Opening 20LW6_TEST_redo.xls

2) Calling the same routine that the Command Button usually does

3) Save and Close 20LW6_TEST_redo.xls and then move on to the next work book .

So far I have (1) done. Here is what I have so far:

Sub RunMixedMetrics()
    Dim MyPath As String
    Dim CurrentWorkBook As String
    Dim WorkBookNames() As Variant
    MyPath = ActiveWorkbook.Path
    'Debug.Print MyPath
    WorkBookNames = Array("20LW6_TEST_redo.xls")
    CurrentWorkBook = WorkBookNames(0)
    Workbooks.Open Filename:=MyPath & "\" & WorkBookNames(0)
    With WorkBookNames(0)
        Call GetOverallData
        Advanced.Show
    End With
    'Workbooks(WorkBookNames(0)).Save
    'Workbooks(WorkBookNames(0)).Close
End Sub

Now the part in Bold does not work. It fails for the obvious reason: Call GetOverallData is not defined since it is in another workbook. So the code will not even compile.

I thought that it was as simple as copying everything under "Private Sub CommandButton1_Click()" to my Script, but I thought wrong.


In summary, my goal is to get my Script in one workbook to open another workbook, 'click' the button for me, and then save and close the workbook. Then, rinse and repeat all weekend.

Any suggestions?

[Bob Phillips]

APplication.Run "20LW6_TEST_redo!GetOverallData"

[Saladsamurai]

Crap! Sorry, I failed miserably. The Routine that the Command Button runs is not what I posted. It should be

Private Sub Analyze_Click()
    ProgressDialog.Show
End Sub

I tried

Application.Run "20LW6_TEST_redo!ProgressDialog.Show"

xld, but I am assuming it needs some modification since it does not work. Says ""20LW6_TEST_redo!ProgressDialog.Show Not Found" or something to that effect.

[Saladsamurai]

I tried

 Application.Run "20LW6_TEST_redo!Analyze_Click"

But it is "Not Found" either. I think that is because it is Private....arg

EDIT Okay. So here is a redefinition of the problem:

How do you call a Private Sub from another workbook?

[Bob Phillips]

YOu don't, you make it Public.

[Saladsamurai]

YOu don't, you make it Public.

Well that is just not an option unfortunately. I'm working with what I have here.

I found this workaround (as horrific as it looks) which I need to modify to suit my needs. It basically finds the button and 'clicks' it for you::

Public Sub RunButton(workbookName As String, worksheetName As String, controlName As String)
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = Workbooks.Open(workbookName)
    wkb.Activate
    Dim obj As OLEObject
    For Each wks In wkb.Worksheets
        If wks.Name = worksheetName Then
            wks.Activate
            For Each obj In wks.OLEObjects
                If (obj.Name = controlName) Then
                    obj.Activate
                    SendKeys (" ")
                End If
            Next obj
        End If
    Next wks
End Sub

But first I need to solve my other thread called "Dim Array() As Workbook??"

[Bob Phillips]

Why can't you make it public?

[Saladsamurai]

Why can't you make it public?

Well, I could. But it seems easier to me to develop a work around for my 1 script as opposed to changing 20 workbooks.

But either way, I made Analyze_Click() Public and I still get the error "20LW6_TEST_redo!Analyze_Click() cannot be found"

What is the problem now? Do I have to further qualify it? Maybe "20LW6_TEST_redo.Sheet4!Analyze_Click()"

?

[Saladsamurai]

...

But either way, I made Analyze_Click() Public and I still get the error "20LW6_TEST_redo!Analyze_Click() cannot be found"

Anyone know what is going on here? 20LW6_TEST_redo is open and Analyze_Click certainly exists AND is Public

[Saladsamurai]

Anyone know what is going on here? 20LW6_TEST_redo is open and Analyze_Click certainly exists AND is Public

This works and I am going with it:

[VBA]Workbooks(WorkBookNames(0)).Worksheets("Viewer").Analyze_Click.Value = True[/VBA]

("Viewer" is the name of the sheet with the clickbutton on it)

[Saladsamurai]

This SUCKS. What the F am I doing wrong now. I used
[VBA]Workbooks(WorkBookNames(0)).Worksheets("Viewer").Analyze_Click.Value = True [/VBA]

I thought is as working. Think again. I run my routine:

Sub RunMixedMetrics()
    Dim MyPath As String
    Dim CurrentWorkBook As String
    Dim WorkBookNames() As Variant
    Dim i As Integer, nCases As Integer, j As Integer
    WorkBookNames = Array("20LW6_TEST_redo.xls")
    MyPath = ActiveWorkbook.Path
    Workbooks.Open Filename:= MyPath & "\" & WorkBookNames(0)
    Workbooks(WorkBookNames(0)).Worksheets("Viewer").Analyze_Click.Value = True
End Sub

It opens the workbook fine and it Even executes the line in bold. My click button routine runs fine. But then when the click-button routine ends I get an error "Object Required" and the debugger highlights the line in bold. WTF? It already executed that line! How can there be a problem with it?

I know this would be easier if I could show you the other routine, but it is huugggeeee and I would never ask anyone to look through it even if I could upload it.

What I can say about the click routine is this:

When clicked, a UserForm pops up with a progress bar in the dialogue. When the routine ends, the Userform does not unload automatically. So I click "Close" on the user form and that is when the error appears.

...arggggggghhhhhh

[Bob Phillips]

If Analyze_Clixk is a procedure, what makes you think it has a Value property?

[Saladsamurai]

If Analyze_Clixk is a procedure, what makes you think it has a Value property?

That fact that Dave Peterson says so.

I am not a programmer, so I don't even know what that means. I just say to myself: "Self, what do you need to do today? Okay then! You better Google that $hit!"

Know what I mean?

But back on track here.....if it does not have a value, like you seem to be implying, then why does [VBA]Workbooks(WorkBookNames(0)).Worksheets("Viewer").Analyze_Click.Value = True [/VBA] actually run the routine (like it clicked the button for me)?



More importantly why does the line that you originally gave me :

Application.Run "20LW6_TEST_redo!Analyze_Click"

not work if I made Analyze_Click Public?

I even created a New Public Sub called Mixed() and put it in "20LW6_TEST_redo" in a module and tried calling that using

Application.Run "20LW6_TEST_redo!Mixed"

But AGAIN Excel says it can't find it....what the hell is its problem.

[Bob Phillips]

But Dave is not running the click procedure, he is getting the value of that combobox, whereas you seem to be trying to run the Analyze_Click procedure. Procedures do not have properties, controls do.

[Saladsamurai]

But Dave is not running the click procedure, he is getting the value of that combobox, whereas you seem to be trying to run the Analyze_Click procedure. Procedures do not have properties, controls do.

Errr. I don't think so....

[QUOTE]You know the codename of that sheet that holds that button, right?

Dim OtherWkbk As Workbook
Set OtherWkbk = Workbooks("Budget Payee Names.xls")
Application.Run "'" & OtherWkbk.Name & "'!sheet1.cbAddPayee_Click"

Replace sheet1 with the codename for worksheet that owns that commandbutton.

Another way if you know the sheetname, but not its codename:

Dim OtherWkbk As Workbook
Set OtherWkbk = Workbooks("Budget Payee Names.xls")
OtherWkbk.Worksheets("Sheetnamehere").cbAddPayee.Value = True[

/QUOTE]


By either way. I really could care less about Dave. i just want this to work. And like i said

Workbooks(WorkBookNames(0)).Worksheets("Viewer").Analyze_Click.Value = True

Does run the sub but it then fails after completion. No matter though. i just ran them all by hand. I couldn't get Application.Run to work either.

[Bob Phillips]

Well you may not think so, but you are wrong. I have read your code, I have read Dave's code, and I know it is so.

I haven't tried yours, but I guess it sees the event, runs it and then when it gets back tries to evaluate the rest. A bit odd, but I can't see any other explanation.

Why don't you just try

Call Workbooks(WorkBookNames(0)).Worksheets("Viewer").Analyze_Click