VBA Workbook.close function: Cannot make this work within required location

Question

I have this code:

Sub test()
    Dim wb As String
    Dim wbb As Workbook
    wb =     "C:\xyz.xlsx"
    Set wbb = Workbooks.Open(wb)
    MsgBox ("testing")
    wbb.Close
End Sub

The above does the job just fine. Workbook closes as expected

This code however, does not seem to work. But to me it looks identical in relation to the workbooks.close function. Can someone please advise why this throws the error "Run-time error '1004': Application-defined or object-defined error"

Sub filesTest(folder, dex)
Dim wb As String
Dim wbb As Workbook
Dim dat
Dim dat2
Dim dat3
Dim dat4

wb = "xyz.xlsm"
    For Each file In folder.Files
        If file.Name Like "*.xlsm" Then
            If InStr(file.Name, "~$") = 0 Then
            'sendFile (file.Name)
                Set wbb = Workbooks.Open(file)
                Worksheets("Sheet 1").Select
               dat = Range("F11")
                Worksheets("Sheet 2").Select
                dat2 = Range("C54")
                dat3 = Range("D54")
                dat4 = Range("E54")
               wbb.Close
            Workbooks(wb).Activate
            Range("B" & dex) = dat
            Range("C" & dex) = dat2
            Range("D" & dex) = dat3
            Range("E" & dex) = dat4
            End If
        End If
    Next
End Sub

I have attempted to move the location of the wbb.close but this does not seem to help at all.

I have also attempted to use an alternative "Activeworkbook.close false" and variations on this with exactly the same error message appearing.

I have also confirmed Set wbb = Workbooks.Open(file) <-- that the file variable has the same format as the code in the test block

Any help would be much appreciated,

Thank you.

So the error comes up on the screen. Click 'debug' and it highlights the line **wbb.Close**. I will add- Following this script ending after the error. A box prompts me to close the spreadsheet i was attempting to close. So it does seem to be getting somewhere? — dazedAndConfused, Dec 29 '15 at 15:46
Is there any code in the `Workbook_Open` event of the workbook that causes the problem? — Rory, Dec 29 '15 at 15:48
the workbooks.open(file) section seems to work perfectly. Is this what you are referring to? Thank you for your advice. — dazedAndConfused, Dec 29 '15 at 15:58
No, I mean is there code in the workbook you are opening (I note they are all macro files) that runs automatically when you open it? Try adding: `Application.EnableEvents = False` before the `Workbooks.Open` line and `Application.EnableEvents = True` immediately after opening it. — Rory, Dec 29 '15 at 16:00
There is code there. I thought you had cracked it after reading your logic - it made sense. Unfortunately editing the code to show this Application.EnableEvents = False Set wbb = Workbooks.Open(file) Application.EnableEvents = True still leads to the same error on wbb.close :/ — dazedAndConfused, Dec 29 '15 at 16:03
Does this code fail on all your workbooks or just some of them? (there isn't actually anything wrong with the code itself) — Rory, Dec 29 '15 at 16:06
RORY YOU ARE A G. **thank you!** I simply left application.enableevents as false until after the wbb was closed. and then avctivated. This works!! Is this simply due to some code within the other workbooks running? — dazedAndConfused, Dec 29 '15 at 16:09
Also is this safe to leave application.enableEvents as false for a longer period than you originally suggested? — dazedAndConfused, Dec 29 '15 at 16:09
Unless you want event code to run, it is safe to disable it for longer as long as you **ensure** it is turned back on again. You could also simply skip selecting sheets - it appears that that may be running code that causes your code to fail. — Rory, Dec 29 '15 at 16:12

score 1 · Answer 1 · answered Dec 29 '15 at 16:16

This is the solution:

Sub filesTest(folder, dex)
Dim wb As String
Dim wbb As Workbook
Dim dat
Dim dat2
Dim dat3
Dim dat4

wb = "xyz.xlsm"
    For Each file In folder.Files
        If file.Name Like "*.xlsm" Then
            If InStr(file.Name, "~$") = 0 Then
        'sendFile (file.Name)
application.enableevents = false
                Set wbb = Workbooks.Open(file)
                Worksheets("Sheet 1").Select
               dat = Range("F11")
                Worksheets("Sheet 2").Select
                dat2 = Range("C54")
                dat3 = Range("D54")
                dat4 = Range("E54")
               wbb.Close
application.enableevents = true
             Workbooks(wb).Activate
            Range("B" & dex) = dat
            Range("C" & dex) = dat2
            Range("D" & dex) = dat3
            Range("E" & dex) = dat4
            End If
        End If
    Next
End Sub

score 0 · Answer 2 · answered Dec 29 '15 at 16:14

This may not solve the issue, but I think part of the problem is the multiple workbooks, without explicit references in your Range. It's worth pointing out, and is too long for a comment.

Try using the below:

Sub filesTest(folder, dex)
Dim wb      As String
Dim wbb     As Workbook
Dim dat, dat2, dat3, dat4

wb = "xyz.xlsm"
For Each file In folder.Files
    If file.Name Like "*.xlsm" Then
        If InStr(file.Name, "~$") = 0 Then
            'sendFile (file.Name)
            Set wbb = Workbooks.Open(file)
            With wbb
                dat = .Worksheets("Sheet 1").Range("F11")
                dat2 = .Worksheets("Sheet 2").Range("C54")
                dat3 = .Worksheets("Sheet 2").Range("D54")
                dat4 = .Worksheets("Sheet 2").Range("E54")
                .Close
            End With

            With Workbooks(wb)
                .Range("B" & dex) = dat
                .Range("C" & dex) = dat2
                .Range("D" & dex) = dat3
                .Range("E" & dex) = dat4
            End With
        End If
    End If
Next
End Sub

What I did was used With to help explicitly state where the ranges being used are. That might help, but if not, let me know.

Thank you for this, this has shown me the correct way to format my code. However, the application enable events flag was to be set for solution — dazedAndConfused, Dec 29 '15 at 16:18
@dazedAndConfused - Gotta love it when it's some tertiary code doing it, and not your own :P. Glad you got it figured out! But yes, I suggest really looking at the above and seeing how I [removed `.Select` so you can work directly with your data](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), and also explicitly tell each `Range` what sheet it's to be from (same would go for using `.Cells()`, `.Rows()`, etc.) — BruceWayne, Dec 29 '15 at 16:20

score 0 · Answer 3 · answered Dec 29 '15 at 16:15

It appears you have event code running in the open event and/or sheet activate events, so you can disable that using EnableEvents. There's also a lot of unnecessary selecting:

Sub filesTest(folder, dex)
Dim wb As String
Dim wbb As Workbook
Dim dat
Dim dat2
Dim dat3
Dim dat4

On Error Goto clean_up
wb = "xyz.xlsm"
    Application.Enableevents = False
    For Each file In folder.Files
        If file.Name Like "*.xlsm" Then
            If InStr(file.Name, "~$") = 0 Then
            'sendFile (file.Name)
                Set wbb = Workbooks.Open(file)

               dat = wbb.Worksheets("Sheet 1").Range("F11").Value
               With wbb.Worksheets("Sheet 2")
                  dat2 = .Range("C54").Value
                  dat3 = .Range("D54").Value
                  dat4 = .Range("E54").Value
               End With
               wbb.Close
            Workbooks(wb).Activate
            Range("B" & dex) = dat
            Range("C" & dex) = dat2
            Range("D" & dex) = dat3
            Range("E" & dex) = dat4
            End If
        End If
    Next

clean_up:
    Application.Enableevents = True
End Sub

VBA Workbook.close function: Cannot make this work within required location

3 Answers3