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.
我有一个工作簿打开另一个工作簿（文件名基于单元格值），然后在该文件中运行一个名为Single_sector的宏。
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
3 个解决方案
#1
13
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
参数是您要传递的参数数组，因此其他工作簿中的sub应该类似于
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
#2
3
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.
请确保您在另一个工作簿中的代码处于Workbook_open事件，因此您不需要使用Run（“Single_sector”）。一旦另一个工作簿打开，过程single_selector将触发。
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
#3
3
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
您在其他Excel文件中有一个Auto_Open Sub来处理要在其他电子表格上运行的宏的位置
#1
13
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
参数是您要传递的参数数组，因此其他工作簿中的sub应该类似于
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
#2
3
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.
请确保您在另一个工作簿中的代码处于Workbook_open事件，因此您不需要使用Run（“Single_sector”）。一旦另一个工作簿打开，过程single_selector将触发。
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
#3
3
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
您在其他Excel文件中有一个Auto_Open Sub来处理要在其他电子表格上运行的宏的位置