VBA to close Workbook doesn't work perfectly

Question

I have the following code, which opens a certain WB, gets some info from it and then closes it. The thing is, that sometimes the WB doesn't get closed correctly. Sometimes, even if the user can't see that the WB is open, Excel is still running it, because if someone else tries to open it (the WB is located in a shared folder), Excel won't let him because it says that someone else has the WB opened.

Dim iStatus As Long
Err.Clear
On Error Resume Next
Set wb2 = Workbooks("Base de Datos Cotizaciones Shared.xlsb")
iStatus = Err
On Error GoTo 0
If iStatus Then
    Workbooks.Open Filename:="\\3kusmiafs02\CAPACITY\Cotizaciones\Base de Datos Cotizaciones Shared.xlsb"
Else
    wb2.Activate
End If

On Error GoTo errHandler:

Worksheets("Data").Activate

maxnum = CLng(Application.WorksheetFunction.Max(Range("A:A")))
quote_num = maxnum + 1

ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Offset(1, 0) = quote_num


Range("A1").Select
Selection.End(xlDown).Select

Dim input_range As Range

Set input_range = Selection.Offset(0, 1)

wb3.Activate
Worksheets("UI VENTAS").Activate
Range("Quote_num") = quote_num
Application.Goto Reference:="Dimstable"
Selection.Copy

Workbooks("Base de Datos Cotizaciones Shared.xlsb").Activate
Worksheets("Dims").Activate

Range("table_start2").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                       xlNone, SkipBlanks:=False, Transpose:=False

For i = 1 To 17
    input_range.Offset(0, i - 1).Value = data_values(i - 1)
Next i
input_range.Offset(0, 32).Value = data_values(17)

Workbooks("Base de Datos Cotizaciones Shared.xlsb").Save
Workbooks("Base de Datos Cotizaciones Shared.xlsb").Close

Is there a way to fix this?

`set` it to an object and close that way. Something like `set wb=Workbooks("Base de Datos Cotizaciones Shared.xlsb")` then later do `wb.close`. — findwindow, Apr 20 '16 at 16:39
That line of code works, VBA closes the WB, but somehow Excel is still running it sometimes (not always). I need to fix that issue... — N. Pavon, Apr 20 '16 at 16:42
You use various references (variables) to workbooks. I suggest (as FindWindow also implies) to set these to Nothing: `Set wb2 = Nothing` and `Set wb3 = Nothing`. — Paul Ogilvie, Apr 20 '16 at 16:50
For the record - I've used shared files before over a network, and have seen the user sitting behind me close the file, and if I open it quickly after that, it tells me it's still in use (sometimes telling me this for a while). This may be more of a Windows issue than VBA/Excel. — BruceWayne, Apr 20 '16 at 16:53
@BruceWayne Exactly, that is actually the problem I'm experiencing. I was wondering if VBA has a function like an emergency shut down to close WBs, or something like that. — N. Pavon, Apr 20 '16 at 17:21
@N.Pavon - Ah, okay yeah I see. What if you did `Application.Quit` after closing the workbook? Although I think that might close any and all open Excel's, it may be what you're needing? See [this thread](http://stackoverflow.com/questions/3628252/closing-excel-application-using-vba) for more explanation. — BruceWayne, Apr 20 '16 at 17:23
@PaulOgilvie If I set them to nothing, how can I use them afterwards? — N. Pavon, Apr 20 '16 at 17:23
@BruceWayne About `Application.Quit`... if the user has more Excel files opened, this will close all of them, right? So if the user is working with other files, he will lose all the changes he did to those files? — N. Pavon, Apr 20 '16 at 17:30
@N.Pavon - I'm pretty sure, yes. So, it's not really a solution, sorry! I just wanted to at least mention it though. Perhaps you can try it with your one workbook open, and if it in fact works, perhaps we can get a workaround? I've been searching, and that's the only other idea to ensure that Excel is closed. I think Windows sees an "artifact" (no idea if that word is right) that a user is in a worksheet, when they aren't. Is there some Excel cache we can clear maybe? — BruceWayne, Apr 20 '16 at 17:35
I noticed such Windows/Application behavior before too (files remain busy even when closed). Usually, opening and closing a few (random) files solves the problem. Maybe this forces some cache to be flushed. — Paul Ogilvie, Apr 20 '16 at 17:37
Yes, I know that if the person experiencing the problem closes all the Excel files that were open, the problem is solved. However, I need somehow a "VBA" solution, so that the tool I'm developing can work without having these kind of problems. I would be very grateful if you come up with a solution (I've tried everything I could come up with). — N. Pavon, Apr 20 '16 at 17:46
Something like: at the end of the code, when everything that had to be done is done, check if the file is somehow busy (I know how to do this) and in that case, shut it down complitely (I don't know how to do this). — N. Pavon, Apr 20 '16 at 17:47
"_I need somehow a "VBA" solution_": then open/close a number of other files in VBA so the Windows(?) cache gets flushed and the file in question gets unlocked. They can be text files opened with `Open`. — Paul Ogilvie, Apr 20 '16 at 18:24
I'm a bit late to the party, but I'll go with the comment from @BruceWayne. I believe it's our network speed here that causes problems - I can open a file, close it again and Windows won't let me open it again as it thinks I've still got it open. I put it down to companies going for the lowest bidder when outsourcing their IT requirements. — Darren Bartrup-Cook, Dec 18 '17 at 16:19

VBA to close Workbook doesn't work perfectly

0 Answers0