vba 尝试打开工作簿并在该文件中运行宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15200349/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trying to open a Workbook and a run a macro in that file
提问by user1923975
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
回答by Excel Developers
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
回答by user2824072
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 来处理要在其他电子表格上运行的宏
回答by user2824072
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
