[RESOLVED] closing all open excel

[unxzst]

this is what i have:

Private Sub CleanExcel()
    On Error GoTo err
ttop:
    Set oExcel = GetObject(, "Excel.Application")
    oExcel.Application.DisplayAlerts = False
    oExcel.Quit
    Set oExcel = Nothing
    GoTo ttop
err:
    Exit Sub
End Sub

I thought I could try to link to an active excel instance untill i get an error and that way close them all. but this is not the case. somtimes it closes only one file, sometimes it gets stuck. the problem is any excel instance i would have open would be hidden, and i cannot see any notify prompts, so it gets stuck even with DisplayAlerts = False
what would be another way to do this?

[Siddharth Rout]

Try this

Dim XLAPP as object
Dim WB As Workbook

do
set xlapp=getobject("Excel.Application")
if xlapp is nothing then exit sub
For each wb in xlapp.workbooks
wb.close 'True/false depending on your requirements
next
'you can also put the code to close xlapp...
loop

Hope this helps...

[unxzst]

Sort of... I have multiple instances of excel open, with one workbook in each. closing workbooks will still leave excel open. I need to loop untill there are no more "Excel.Application" left, but that doesen't always seem to work with my code.

if i step through the sub, it works. but running on its own closes at most one instance of excel, if any.

i think i know whats causing it to get stuck. i get a message from excel "hidden.xls is now available for editing, choose read-write to open it for editing." i''m gussing that i didnt create it right, and now this is causing my problem.
i make my excel files like this:

Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.workbooks.Add
Set hExcel = CreateObject("Excel.Application") 
Set hWB = hExcel.workbooks.Open("C:\hidden.xlsx")

[Siddharth Rout]

This will close any instance of excel that is open. You can combine it with the code that I gave above...

Sub aaa()

Dim Flag As Boolean
Flag = True

On Error Resume Next

Do Until Flag = False
    Dim XLAPP As Object
    Set XLAPP = GetObject(, "Excel.Application")
    If XLAPP Is Nothing Then
        Flag = False
        Exit Do
    Else
        XLAPP.Quit
        Set XLAPP = Nothing
        'Strange enough, if you remove this message box
        'This code won't run as expected!!!!
        MsgBox "An Instance of Excel was Closed"
    End If
Loop

End Sub

Hope this helps...

[Jrogers]

are you doing this from Excel vba or a vb app

[anhn]

Combine Post#1 and Post#2 and noted that err is a reserved word.

Private Sub CleanExcel()
    Dim oExcel as Object
    Dim wb as Object

    On Error GoTo Done
    Do
        Set oExcel = GetObject(, "Excel.Application")
        oExcel.Application.DisplayAlerts = False
        For Each wb In oExcel.Workbooks
            wb.Close False
        Next
        oExcel.Quit
    Loop
Done:
    Set oExcel = Nothing
End Sub

[unxzst]

Thanks for your help, anhn, but your code does not work, it is stuck in the loop not going anywhere.

I'm working from VB6, and this code works:

On Error GoTo Done
ttop:
    Set oExcel = GetObject(, "Excel.Application")
    oExcel.Application.DisplayAlerts = False
    oExcel.Quit
    Set oExcel = Nothing
    GoTo ttop
Done:
    Exit Sub

but only when stepping through it, if its running only one excel file closes at most.
maybe setting oExcel = Nothing is not enough, it does not latch on to another window and gets an error?

[Siddharth Rout]

did you try my code in post 4?

[unxzst]

This will close any instance of excel that is open. You can combine it with the code that I gave above...

Sub aaa()

Dim Flag As Boolean
Flag = True

On Error Resume Next

Do Until Flag = False
    Dim XLAPP As Object
    Set XLAPP = GetObject(, "Excel.Application")
    If XLAPP Is Nothing Then
        Flag = False
        Exit Do
    Else
        XLAPP.Quit
        Set XLAPP = Nothing
        'Strange enough, if you remove this message box
        'This code won't run as expected!!!!
        MsgBox "An Instance of Excel was Closed"
    End If
Loop

End Sub

Hope this helps...

i think adding XLAPP.Application.DisplayAlerts = False will help, because the closing warning dialog halts any progress.
i'm also stumped as to why it doesent work without some kind of intermediary step...

[Siddharth Rout]

i'm also stumped as to why it doesent work without some kind of intermediary step...

Yeah I know

I haven't tried it with DoEvents. Try that in lieu of the msgbox. See if it helps? but did the code work?

[unxzst]

thanks, the code works.
only thing is it doesen't catch weird excel messages, for example if more than one instance of the same file is opened....
and...its better off with MSGBox than DoEvents. with doevents only one instance is closed.

killing off excel is harder than expected...gr

[Siddharth Rout]

doesen't catch weird excel messages

What messages?

I opened several excel instance and ran the code and all of them closed without any message(s) except the message which is there in the code....

[unxzst]

What messages?

I opened several excel instance and ran the code and all of them closed without any message(s) except the message which is there in the code....

something about notifying the other instance of the file...I'm using 2007, btw.
DisplayAlerts = False seems to work.

[unxzst]

i guess its as resolved as it'll get. thanks for your help.
tell me, do you know anything else about VB6 working with Excel? Like getting ranges into array fast and efficiently, or reading large amounts of data in and out all the time? i'd be glad to know what you can tell me. thanks again.

right now i'm doing myarray = oExcel.Range(oExcel.Cells(13, 1), oExcel.Cells(466, 1)).Value
but what if my data is in checker formation, or in five cells spread through the worksheet? can i define a range to be a collection of points like that?