6

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Prashant Kumar
  • 20,069
  • 14
  • 47
  • 63
  • FWIW, your code seems to work for me on Windows Excel 2010 with a user form (and also as plain code). – stephan May 16 '12 at 05:37
  • Darn :( I suppose I should test it on Windows myself now. Though I'd love to see a solution that works for both Windows and Mac. – Prashant Kumar May 16 '12 at 05:38

2 Answers2

7

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • What a silly bug. Moved the `Unload Me` to below the `Dim` statements and it works. Thank you! (I also added some `ScreenUpdating = False`/`True` lines to smooth it out a little). – Prashant Kumar May 16 '12 at 13:46
1

I had this exact problem on Excel 11 on Mac (Worked fine Excel 2013 on Windows), only the error occurred in a module sub that was called from the UserForm. If somebody (like me) is trying to use the workbook.close method from a sub/function in a module (or another location) that is not inside the UserForm itself you can't use 'Me'. 'Me' is only usable within the UserForm code itself.

Instead of 'Unload Me' use the unload function and the name of your UserForm. 

Unload UserFormName
Hopploppa
  • 142
  • 9