vbaexcel

Trying to open a Workbook and a run a macro in that file


I have a workbook which opens up another workbook (filename is based on a cell value) and then runs a macro called Single_sector within that file.

It opens the file perfectly fine but doesn't run the macro. Any ideas?

Sub run_all()
Dim Location



On Error Resume Next

'Location of file to open
 Location = Worksheets("Main").Range("folder_location").Value

'Open F&V File
Application.Workbooks.Open Location & Range("fv_file").Value
'Run Macro
Run ("Single_sector")



End Sub

Solution

  • Place the following code in the macro calling the other workbook:

    Location = Worksheets("Main").Range("folder_location").Value
    Set wb = Workbooks.Open(Location & Range("fv_file").Value)
    Application.Run "'" & wb.Name & "'!" & strSubToRun, Parameters
    Set wb = Nothing
    

    Parameters is an array of arguments that you want to pass, so the sub in the other workbook should look something like

    Public Sub TheSub(ParamArray X())
    
    Dim i As Long
    
    Sheet1.Cells(1, 1).Value = "Parameters passed:"
    
    For i = 0 To UBound(X(0))
        Sheet1.Cells(i + 2, 1).Value = CStr(X(i))
    Next
    
    End Sub