在 VBA 中出现“无法运行宏...”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18244653/
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
Getting "Cannot Run the Macro..." Error in VBA
提问by user2684387
I recently learned how to program in VBA and have gotten some extremely helpful assistance by reading through the previously asked questions and answers on this site. However, I have encountered a problem in one of my programs that doesn't appear to be addressed very directly. So I'll ask it here.
我最近学习了如何在 VBA 中编程，并通过阅读本网站上先前提出的问题和答案获得了一些非常有用的帮助。但是，我在我的一个程序中遇到了一个似乎没有直接解决的问题。所以我会在这里问。
I am making a Powerpoint macro that will automatically update some slides with new data. For one slide that contains an excel table, I want the macro to open up an excel file, run an existing macro in the excel file to populate the spreadsheet with new data, and finally copy the table over to the powerpoint slide. My code so far (without the copy over portion) is this:
我正在制作一个 Powerpoint 宏，它会自动用新数据更新一些幻灯片。对于一张包含 excel 表格的幻灯片，我希望宏打开一个 excel 文件，在 excel 文件中运行现有的宏以使用新数据填充电子表格，最后将表格复制到 powerpoint 幻灯片。到目前为止我的代码（没有复制部分）是这样的：
Private Sub GetProposals()
Dim myXL As Excel.Application
Dim myXLS As Excel.Workbook
Dim ws As Excel.Worksheet
Set myXL = New Excel.Application
Set myXLS = GetObject("K:\Hymanson\Proposal Summary Master.xlsm")
Set ws = myXLS.Sheets(1)
ws.Visible = xlSheetVeryHidden
myXLS.Sheets("VLOOKUP").Range("J1").Value = "EPL"
myXL.Run ("'K:\Hymanson\Proposal Summary Master.xlsm'!BABox_Change")
End Sub
It runs correctly until I reach the "myXL.Run..." line. I get a message saying "Run-time error '1004': Cannot run the macro "K:\Hymanson\Proposal Summary Master.xlsm'!BABox_Change'. The macro may not be available in this workbook or all macros may be disabled."
它运行正常，直到我到达“myXL.Run...”行。我收到一条消息，说“运行时错误‘1004’：无法运行宏‘K:\Hymanson\Proposal Summary Master.xlsm’！BABox_Change”。宏在此工作簿中可能不可用，或者可能禁用了所有宏。”
The excel macro runs fine when I open up the file directly and start it that way. I am somewhat stuck on what I should do next. Does anyone have some suggestions?
当我直接打开文件并以这种方式启动时，excel 宏运行良好。我对下一步应该做什么有些困惑。有没有人有一些建议？
采纳答案by Jaycal
The issue may be because you're not opening the workbook. The key to this though, would be to make sure that the objects are released after the code is executed; that way, the file isn't "locked" by your powerpoint file or extra 'hidden' processes/instances of Excel are left open.
问题可能是因为您没有打开工作簿。不过，关键是要确保在执行代码后释放对象；这样，该文件就不会被您的 powerpoint 文件“锁定”，或者 Excel 的额外“隐藏”进程/实例保持打开状态。
Private Sub GetProposals()
Dim myXL As Excel.Application
Dim myXLS As Excel.Workbook
Dim ws As Excel.Worksheet
Set myXL = New Excel.Application
Set myXLS = myXL.Workbooks.Open("K:\Hymanson\Proposal Summary Master.xlsm")
myXLS.Application.DisplayAlerts = False
Set ws = myXLS.Sheets(1)
ws.Visible = xlSheetVeryHidden
myXLS.Sheets("VLOOKUP").Range("J1").Value = "EPL"
myXLS.Application.Run ("BABox_Change")
myXLS.Application.DisplayAlerts = True
myXLS.Close(true) ' Change to false if you don't want to save Changes
Set myXLS = Nothing
Set myXL = Nothing
Set ws = Nothing
End Sub
回答by leroy
Brilliant. that worked for me as well. I just adapted the portion
杰出的。这对我也有用。我只是调整了部分
myXLS.Sheets("VLOOKUP").Range("J1").Value = "EPL"
myXLS.Application.Run ("BABox_Change")
to my files
到我的文件
