在VBA Userform中关闭打开的工作簿时出错 [英] Error when closing an opened workbook in VBA Userform

问题描述

在子程序中，我想打开一个工作簿，从中读取一些工作簿，并关闭它。

由于某些原因，我收到错误：

 运行时错误'1004'：
 
方法关闭对象_Workbook失败
  / pre> 
 
 
我已经确定了一个最小的代码段来重现问题。
 
创建一个新的excel文件。在其中创建一个Userform。在此，使用以下Click事件代码创建一个命令按钮：
  Private Sub CommandButton1_Click（）
 Dim filename As String 
 Dim opened_workbook As Workbook 
 
 filename = Application.GetOpenFilename（）'用户选择有效的Excel文件
设置opens_workbook = Application.Workbooks.Open（filename）
'文件操作将在这里发生
打开_workbook.Close'这里抛出的异常
 
 MsgBox如果你到这里，它的工作！ 
卸载我
结束Sub 
  
真正让我困惑的是这个错误当命令按钮不在用户窗体（工作表上的普通按钮上）时，不会发生相同的代码。
 
 
 
我甚至不知道还有什么报告或在哪里寻找解释这个行为（除了StackOverflow！）。我正在使用Excel for Mac 2011编写VBA，如果它有所作为，可以移至Windows Excel 2010。
解决方案
是的，在Excel 2011中，它是一个错误（未记录 - 我还没有找到它的文档）。你必须稍微修改代码。尝试这个
  Private Sub CommandButton1_Click（）
 Dim filename As String 
 Dim opened_workbook As Workbook 
 
 filename = Application.GetOpenFilename（）'用户选择有效的Excel文件
设置opens_workbook = Application.Workbooks.Open（filename）
 
卸载我
 
 opened_workbook.Close 
 
 MsgBox如果你到了这里，它工作！ 
 End Sub 
  
 
In a subroutine, I want to open a workbook, do some reading from it, and close it.

For some reason, I get an error:
Run-time error '1004':

Method 'Close' of object _Workbook failed
I have identified a minimal code snippet to reproduce the problem.

Create a fresh excel file. In it, create a Userform. On that, create a Command Button with the following Click event code:
Private Sub CommandButton1_Click()
    Dim filename As String
    Dim opened_workbook As Workbook

    filename = Application.GetOpenFilename()    ' User selects valid Excel file
    Set opened_workbook = Application.Workbooks.Open(filename)
    ' File operations would occur here
    opened_workbook.Close    ' Exception thrown here

    MsgBox "If you got here, it worked!"
    Unload Me
End Sub
What really perplexes me is that this error doesn't happen with the same code when the Command button is not on a userform (on a plain button straight on the worksheet).

I don't even know what else to report or where to look to explain this behavior (besides StackOverflow!). I'm writing VBA using Excel for Mac 2011 and can move to Windows Excel 2010 if it makes a difference.
解决方案
Yes, in Excel 2011, it is a bug (Undocumented - I haven't found a documentation for it yet). You have to slightly modify the code. Try this
Private Sub CommandButton1_Click()
    Dim filename As String
    Dim opened_workbook As Workbook

    filename = Application.GetOpenFilename()    ' User selects valid Excel file
    Set opened_workbook = Application.Workbooks.Open(filename)

    Unload Me

    opened_workbook.Close    

    MsgBox "If you got here, it worked!"
End Sub


                        
这篇关于在VBA Userform中关闭打开的工作簿时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！