获取“无法运行宏...” VBA错误 [英] Getting "Cannot Run the Macro..." Error in VBA

问题描述

我最近学会了如何在VBA中进行编程，并通过阅读本网站上面提到的问题和答案获得了非常有帮助的帮助。但是，我在我的一个程序中遇到了一个似乎没有被直接解决的问题。所以我会在这里问问。

我正在制作一个Powerpoint宏，它会自动使用新数据更新幻灯片。对于包含excel表的一张幻灯片，我希望宏打开一个excel文件，在excel文件中运行一个现有的宏，用新数据填充电子表格，最后将该表复制到powerpoint幻灯片。我的代码到目前为止（没有副本超过部分）是这样的：

  Private Sub GetProposals（）
 Dim myXL As Excel.Application 
 Dim myXLS As Excel.Workbook 
 Dim ws As Excel.Worksheet 
 
设置myXL =新建Excel.Application 
设置myXLS = GetObject（K：设置ws = myXLS.Sheets（1）
 ws.Visible = xlSheetVeryHidden 
 
 myXLS.Sheets（VLOOKUP） .Range（J1）。Value =EPL
 myXL.Run（'K：\Jackson\Proposal Summary Master.xlsm'！BABox_Change）
 End Sub 
  

它运行正常，直到我达到myXL.Run ...行。我收到一条消息说运行时错误1004'：无法运行宏K：\Jackson\Proposal Summary Master.xlsm'！BABox_Change'。此工作簿中的宏可能不可用，或者所有宏可能被禁用。

当我直接打开文件并以这种方式启动时，excel宏运行正常。我有点坚持下一步应该做什么，有没有人有一些建议？

解决方案

问题可能是因为你这不是打开工作簿，但关键在于，确保在执行代码后释放对象;这样，该文件不会被您的powerpoint文件或额外的隐藏进程/实例锁定的Excel将保持打开状态。

  Private Sub GetProposals（）
 Dim myXL As Excel.Application 
 Dim myXLS作为Excel.Workbook 
 Dim ws As Excel.Worksheet 
 
设置myXL =新建Excel.Application 
设置myXLS = myXL.Workbooks.Open（K：\Jackson\\ \\ Proposal Summary Master.xlsm）
 myXLS.Application.DisplayAlerts = False 
设置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）'如果不想保存更改为false 
 
 
设置myXLS =没有
设置myXL = Nothing 
设置ws = Nothing 
 End Sub 
  

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.

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:

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:\Jackson\Proposal Summary Master.xlsm")
    Set ws = myXLS.Sheets(1)
    ws.Visible = xlSheetVeryHidden

    myXLS.Sheets("VLOOKUP").Range("J1").Value = "EPL"
    myXL.Run ("'K:\Jackson\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:\Jackson\Proposal Summary Master.xlsm'!BABox_Change'. The macro may not be available in this workbook or all macros may be disabled."

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?

解决方案

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.

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:\Jackson\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

这篇关于获取“无法运行宏...” VBA错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！