Trying to open a workbook and run a macro in this file

Question

I have a workbook that opens another workbook (the file name is based on the value of the cell) and then runs a macro called Single_sector in this file.

It opens the file perfectly, but does not 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 

Answer 1

Put the following code in a macro that calls another 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 another book should look something like this:

 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 

Answer 2

Please make sure your code in another workbook is in Workbook_open, so you do not need to use Run ("Single_sector"). The single_selector procedure will start as soon as another book opens.

Updated Answer

 Sub run_all() Dim Location On Error Resume Next Dim wkb As Workbook 'Location of file to open Location = Worksheets("Main").Range("folder_location").Value 'Open F&V File Set wkb = Workbooks.Open(Location & Range("fv_file").Value) wkb.Sheets(1).Single_sector ' kindly put this proc in another workbook sheet1 End Sub 

Answer 3

Probably not very elegant, but:

 Dim Location As String Location = "\\location\to\file.xlsm" Workbooks.Open(Location).RunAutoMacros (xlAutoOpen) 

If you have Auto_Open Sub in another excel file to handle macros to work in another table