CODEWITHSUNDEEP
vbaexcel
user1923975
user1923975

Reputation: 1389

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

Upvotes: 6

Views: 55903

Answers (3)

user2824072
user2824072

Reputation: 41

Probably not very elegant but:

Dim Location As String

Location = "\\location\to\file.xlsm"

Workbooks.Open(Location).RunAutoMacros (xlAutoOpen)

Where you have an Auto_Open Sub in your other excel file to handle the macros to run on your other spreadsheet

Upvotes: 4

Excel Developers
Excel Developers

Reputation: 2825

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

Upvotes: 21

user2063626
user2063626

Reputation:

enter image description here

Please make sure your code in another workbook is at Workbook_open event so you dont need to use Run ("Single_sector"). The procedure single_selector would trigger as soon as another workbook is open.

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

Upvotes: 3

Related Questions