Error handling to kill Excel applications

Hi,

Can I caution against this.

What if the user has excel open doing something?

Surely, better to create your own instance of excel (totally separate from any pre-existing ones) from Access, and use that.  Once complete, you can then safely kill the instance that was created from Access without risking any negative outcomes for the user's other excel workbooks that might be open.

Alan.

Alan, I got your point. Maybe it's good if I give you a sample of my code here:

My concern is if the import is unsuccessful. A hidden EXCEL.EXE will remain in process. I want to get rid of it.

Public Sub ImportSomething()
 
On Error GoTo ImportXL_Error
 
     Const xlPath As String = "Y:\myFile.xls" 'FX KO/Fixings
     Const xlPathTemp As String = "Y:\TEMP_myFile.xls"
 
     Dim acExcel, wb
     Dim getRow As Long
     Set acExcel = CreateObject("Excel.Application")
     acExcel.Visible = False
 
     Set wb = acExcel.Workbooks.Open(Filename:=xlPath, ReadOnly:=True)
          
     wb.Worksheets("Sheet1").Range("A:A,C:C,E:K,M:O,R:R").Delete shift:=xlLeft
 
     wb.SaveCopyAs xlPathTemp
 
     wb.Close False
     Set wb = Nothing
     acExcel.Quit
     Set acExcel = Nothing
                                
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, _
        "TempTable", xlPathTemp, True, "Sheet1!A:E"
                               
     Kill xlPathTemp
 
ImportXL_Exit:
    Exit Sub
 
ImportXL_Error:
    Select Case Err
        Case Else
             Select Case MsgBox("An unexpected error has occurred in importing" & vbCrLf & vbCrLf & _
                                "Error" & vbTab & "Description" & vbCrLf & _
                                Err & vbTab & Err.Description, vbCritical + vbAbortRetryIgnore, "Error")
                 Case vbAbort:  Resume ImportXL_Exit
                 Case vbRetry:  Resume
                 Case vbIgnore: Resume Next
             End Select
    End Select
 
End Sub

Select allOpen in new window

Hi,

I may be missing the point, but what if you put in new lines between the existing lines 29 and 30 thus:

acExcel.Quit
Set acExcel = Nothing

That way, if I read your code correctly, then if the user chooses 'Abort' in the error section, then the instance of excel you created will be killed?

Apologies if I am missing something obvious!

Alan.

What if an error occurs before the transferspreadsheet? If user chooses abort, the Excel is still running right?

Hi,

I don't *think* so.  You set the error trap right up front, so if it errors before the transferspreadsheet (somewhere up to line 23) then, with the addition of those lines to the error trap code, it should kill the excel instance that was created.

Do you have reason to think this isn't working?

I guess if Access dies totally (the code ceases to run) then you could be left with an orphaned Excel instance with no window associated with it, and hence no way for a (normal) user to get rid of it.

How likely is that?

Alan.

Sorry. I'm not quite familiar with error handling.

Let's say if error occurs at line 15, and user decides to abort, it goes directly to line 29 and exit the sub isn't it?

Yes, hence my suggestion to add the following two lines of code between what is currently lines 29 and 30:

acExcel.Quit
Set acExcel = Nothing

That way, if it errors out, then the instance of excel you created (acExcel) will be killed.

Does that make sense?

Alan.

OKay. I see. But in this case what if the error occurs at line 24 and 25? When user chooses to abort, there will not be acExcel...

In which case there is no orphaned instance of excel to worry about?

However, you should probably add a check in with those two additional lines to check whether acExcel is empty or not before doing the quit:

Something like:

    If Not IsEmpty(acExcel) Then
   
        MsgBox "Killing"
       
        acExcel.Quit
           
        Set acExcel = Nothing
       
    End If


HTH,

Alan.

Sorry - you can lose the MsgBox - that was just me checking the syntax!

Alan.

Gary,

Thanks for providing the code. I don't quite understand it though :P

Is "kernel32" a user name? Fyi, I'm going to put the database on network so that multiple users can have the access. Will this work?

Open a new module & put this code in it as is.
Wherever you want your code to run simoply add
Call KillExcel