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?