Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error handling to kill Excel applications

Posted on 2009-04-29
13
Medium Priority
?
458 Views
Last Modified: 2013-11-29
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~
0
Comment
Question by:LizzJ
  • 6
  • 5
  • 2
13 Comments
 
LVL 21

Expert Comment

by:Alan
ID: 24258268
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
 

Author Comment

by:LizzJ
ID: 24258296
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
 
LVL 21

Expert Comment

by:Alan
ID: 24258318
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:LizzJ
ID: 24258345
What if an error occurs before the transferspreadsheet? If user chooses abort, the Excel is still running right?
0
 
LVL 21

Expert Comment

by:Alan
ID: 24258367
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
 

Author Comment

by:LizzJ
ID: 24258511
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
 
LVL 21

Expert Comment

by:Alan
ID: 24258529
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
 

Author Comment

by:LizzJ
ID: 24258581
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
 
LVL 21

Expert Comment

by:Alan
ID: 24258621
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
 
LVL 21

Expert Comment

by:Alan
ID: 24258626
Sorry - you can lose the MsgBox - that was just me checking the syntax!

Alan.
0
 
LVL 46

Accepted Solution

by:
tbsgadi earned 1500 total points
ID: 24322937
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
 

Author Comment

by:LizzJ
ID: 24332418
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24333782
Open a new module & put this code in it as is.
Wherever you want your code to run simoply add
Call KillExcel
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question