Error closing open book in custom VBA form

Question

In a subroutine, I want to open a book, read it, and close it.
For some reason, I get an error:

Run-time error '1004': Method 'Close' of object _Workbook failed 

I defined a minimal piece of code to reproduce the problem.
Create a new excel file. In it, create a Userform. In doing so, 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 puzzles me is that this error does not happen with the same code when the Command button is not on the user form (on a simple button right on the sheet).

I don’t even know what else to report or where to look for an explanation of this behavior (besides StackOverflow!). I am writing VBA using Excel for Mac 2011 and can upgrade to Windows Excel 2010 if that matters.

Answer 1

Yes, in Excel 2011 this is an error (Undocumented - so far I have not yet found the documentation). You should slightly change the code. try it

 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 

Answer 2

I had this exact problem in Excel 11 on a Mac (Excel 2013 works fine on Windows), only an error occurred in the sub module that was called from UserForm. If someone (like me) is trying to use the workbook.close method from a sub / function in a module (or another place) that is not inside UserForm itself, you cannot use "Me". "Me" is used only in the UserForm code itself.

Instead of Unload Me, use the upload function and the name of your UserForm.

 Unload UserFormName