Open Closed Workbook and Run Macro

Question

I am trying to open different Excel workbooks, run their respective macros, save, and close. So far I have this code below:

Sub AllFiles()

    Application.Run "'L:\RESEARCH\Alternative Assets\Private Equity\PE Preqin Data\Preqin_Fundraising_Data_Macro.xlsm'!Get_File"

End Sub

The problem with this code is that although it opens the workbook, I keep getting a run-time error '9' subscript out of range. My guess is that the macro is unable to find the certain sheets or variables within the "active" workbook. Here's the code where I get the error (macro in another workbook)

Public Sub Get_File()

    Dim sFiletype As String     'Fund type reference
    Dim sFilename As String     'File name (fund type + date of download), if "" then default
    Dim sFolder As String       'Folder name (fund type), if "" then default
    Dim bReplace As Boolean     'To replace the existing file or not
    Dim sURL As String          'The URL to the location to extract information
    Dim pURL As String
    Dim Cell, Rng As Range
    Dim Sheet As Worksheet

    Dim oBrowser As InternetExplorer
    Set oBrowser = New InternetExplorer

    'Initialize variables
    Set Rng = Range("I2:I15")
    Set Sheet = ActiveWorkbook.Sheets("Macro_Button") 'POINT OF ERROR

    For Each Cell In Rng
        If Cell <> "" Then
        sFiletype = Cell.Value
        sFilename = sFiletype & "_" & Format(Date, "mmddyyyy")
        sFolder = Application.WorksheetFunction.VLookup(Cell.Value, Sheet.Range("I2:Z15"), 2, False)
        bReplace = True
        sURL = "www.preqin.com"
        pURL = Application.WorksheetFunction.VLookup(Cell.Value, Sheet.Range("I2:Z15"), 16, False)

        'Download using the desired approach, XMLHTTP / IE
            If Application.WorksheetFunction.VLookup(Cell.Value, Sheet.Range("I2:Z15"), 15, False) = 1 Then
            Call Download_Use_IE(oBrowser, sURL, pURL, sFilename, sFolder, bReplace)
            Else
            Call Download_NoLogin_Use_IE(oBrowser, pURL, sFilename, sFolder, bReplace)
            End If

        Else: GoTo Exit_Sub
        End If
    Next

Exit_Sub:

    'Close IE
    oBrowser.Quit

End Sub

Anyone have any solution/suggestions to this error? Thanks!

Ralph Ralph · Accepted Answer · 2016-02-16T18:08:06

Use ThisWorkbook instead of ActiveWorkbook. Then you should be fine to run a remote macro.

Set Sheet = ThisWorkbook.Sheets("Macro_Button")

Note: I would always recommend to use ThisWorkbook instead of ActiveWorkbook as it is more explicit. You can never be sure which is the active workbook. But ThisWorkbook always refers to the workbook holding the macro.

Another note (based on your below comments): The above code is not designed to be used with several Workbooks. It is NOT sufficiently explicit. Example:

Set Rng = Range("I2:I15")

In this line you are NOT saying which Sheet or even Workbook you are referring to. So, VBA makes the decision for you. I guess VBA "decides" for something you don't want. So, you'll have to tell VBA what you want and explicitly state that. Here are some possible examples on how to change the above line.

Set Rng = ThisWorkbook.Sheets(1).Range("I2:I15") 'This will be a sheet in the remote Excel file
Set Rng = ActiveWorkbook.Sheets(1).Range("I2:I15") 'This will be a sheet in the file from which you were originally calling the external macro
Set Rng = Workbooks("someFile.xls").Sheets("someSheetInThatFile").Range("I2:I15") 'This would be a third Excel file: not the one calling the macro nor the Excel file hosting the macro

Yet, this is just one line in the above code you'll have to change to make it work across several Excel files.

Open Closed Workbook and Run Macro

1 Answers