Close Excel Window

[fredlo2010]

Hi everyone,

I have this Excel workbook "book1" that used dynamic references to another workbook "book2". In order for the references to work "book2" has to be opened. Correct me if I am wrong I have searched the whole web and I couldn't find a way to do it without opening it. Why I have it set it like that. The answer is file size it dropped about 2 mb. If you guys know a better way to do this please let me know. Its a little annoying to open more than one file at the time.

So I set up an even on the workbook open to automatically open the reference every time the I open the file. It looks like this and its working fine.

My problem is when I try to close the workbook with a Workbook_BeforeClose event.

Here is the code. The workbook closes but the Windows application stays open. So I need to check if the Reference is open and if its and there is no other workbook open the close the application.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
On Error Resume Next
Workbooks("Reference Workbook.xlsx").Close
Application.DisplayAlerts = True
Application.Quit
End Sub

Thanks a lot

[CatDaddy]

try

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Application.DisplayAlerts = False 
    On Error Resume Next 
    Workbooks("Reference Workbook.xlsx").Close
    On Error Goto 0 
    Application.DisplayAlerts = True 
    Application.Quit 
End Sub 

[fredlo2010]

Thanks for the reply.

No Still the same the workbook closes but the Excel Application windows stays open.

[CatDaddy]

try closing the application before the workbook, if the code is in the workbook the macro is closing with the workbook.close event

[CodeNinja]

This worked fine for me....

I did add that the workbooks.saved = true so I don't get those annoying messages, but even without that it closed the other workbook, and exited the application.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Saved = True
    Workbooks("Book3.xlsx").Saved = True
    Application.DisplayAlerts = False
    On Error Resume Next
    Workbooks("Book3.xlsx").Close
    Application.DisplayAlerts = True
    Application.Quit
End Sub

[Aussiebear]

if you don't know which workbooks are open at the time of trying to close Excel then have a look at Justinlebenne code in the Knowledgebase
http://www.vbaexpress.com/kb/getarticle.php?kb_id=718

[fredlo2010]

Thanks a lot guys for the reply. Nothing worked I tried everything I have searched the web and nothing. In theory it should be working.

Well I hate doing this but I will give up on this one. In part because of this issue and also because my gain in file size took its toll in performance. My excel is much slower now with the dynamic ranges and the outside references.

I would highly appreciated if you guys know another way of doing this. I just want to VLOOKUP values from a huge data base. Maybe you can point me the direction to go.

I am sorry for all the time invested in this thread.

Thanks again

[LukeHeavens]

In case you're still looking for an answer after all these years...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
 If Workbooks.Count = 1 Then
    Application.Quit
 Else
    ActiveWindow.Close
 End If
End Sub