Error handling to kill Excel applications

Hi Experts,

I have a Access application with codes that call Excel. I'm looking for error handlers to check if Excel applications exists, if so, kill it.
And can anyone provide good readings on building error handlers?

Thanks~
LizzJAsked:
Who is Participating?
 
tbsgadiConnect With a Mentor Commented:
Hi LizzJ,

This is the code I use for Killing Excel


Good Luck!

Gary
Option Compare Database
Option Explicit 
Public Declare Function IsWindow Lib "user32" (ByVal hwnd As Long) As Long
Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Declare Function TerminateProcess Lib "kernel32" (ByVal hProcess As Long, ByVal uExitCode As Long) As Long
Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function GetWindowThreadProcessId _
   Lib "user32" _
   (ByVal hwnd As Long, _
    lpdwprocessid As Long) As Long 
Public Const PROCESS_ALL_ACCESS = &H1F0FFF
Public hWindowExcel As Long 
Public Sub KillExcel() 
  Dim lngRtn As Long
  Dim lngProc As Long
  Dim lngProcID As Long 
  Dim hThread As Long
  
 
  If (IsWindow(hWindowExcel) <> 0) Then
    hThread = GetWindowThreadProcessId(hWindowExcel, lngProcID)
  
    If (lngProcID <> 0) Then
     
      lngProc = OpenProcess(PROCESS_ALL_ACCESS, CLng(0), lngProcID)
      lngRtn = TerminateProcess(lngProc, CLng(0))
      Call CloseHandle(lngProcID)
    End If
  
  End If
  hWindowExcel = 0
End Sub

Open in new window

0
 
AlanConsultantCommented:
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.
0
 
LizzJAuthor Commented:
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

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
AlanConsultantCommented:
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.
0
 
LizzJAuthor Commented:
What if an error occurs before the transferspreadsheet? If user chooses abort, the Excel is still running right?
0
 
AlanConsultantCommented:
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.
0
 
LizzJAuthor Commented:
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?
0
 
AlanConsultantCommented:
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.
0
 
LizzJAuthor Commented:
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...
0
 
AlanConsultantCommented:
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.
0
 
AlanConsultantCommented:
Sorry - you can lose the MsgBox - that was just me checking the syntax!

Alan.
0
 
LizzJAuthor Commented:
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?
0
 
tbsgadiCommented:
Open a new module & put this code in it as is.
Wherever you want your code to run simoply add
Call KillExcel
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.