Solved

Excel isn't closing!

Posted on 2000-03-16
6
173 Views
Last Modified: 2008-01-16
I have a form with 4 text boxes and a save button. The info in the first box is for searching an excel database and the other three are for filling in the data in the particular found row. This is the code I have which deals with the excel part of the program. My problem is that Excel is not closing completely. When you check ctrl-alt-del you still see it open. Any ideas what I've got wrong here! By the way, the problem arises when the user enters data on more than one occasion without closing out of the form each time.

Option Explicit
Private objExcel As Excel.Application
Private objWorkbook As Excel.Workbook
Private objWorksheet As Excel.Worksheet

Dim rngExcel As Excel.Range
Dim intRow As Integer

Private Sub cmdSave_Click()

 On Error GoTo shoot
 Set objExcel = CreateObject("Excel.Application")
 Set objWorkbook = objExcel.Workbooks.Open("c:\Test Results\Powders.xls")
 Set objWorksheet = objExcel.Workbooks(1).Worksheets(2)
 'Find and Fill Cells
 With objExcel
  Set rngExcel = objWorksheet.Columns(1).Find(txtBagNo.Text)
  intRow = rngExcel.Row
  objWorksheet.Cells(intRow, 5) = txtFe.Text
  objWorksheet.Cells(intRow, 6) = txtMn.Text
  objWorksheet.Cells(intRow, 4) = txtRatio.Text
 End With
 'Error Control
 If txtBagNo.Text = "" Or txtFe.Text = "" Or txtMn.Text = "" Or txtRatio.Text = "" Then
    MsgBox "You have left an input box blank." & vbCrLf & "Please fill in all information."
    objWorkbook.Close savechanges:=False
    objExcel.Quit
    Set objExcel = Nothing
    Set objWorkbook = Nothing
    Set objWorksheet = Nothing
 Else
    objWorkbook.Save
    objExcel.Quit
    Set objExcel = Nothing
    Set objWorkbook = Nothing
    Set objWorksheet = Nothing
    txtBagNo.Text = ""
    txtFe.Text = ""
    txtMn.Text = ""
    txtRatio.Text = ""
 End If
 Exit Sub
shoot:
 If intRow = 0 Then
    MsgBox "The Bag Number you entered was an invalid number. Try again!"
    objWorkbook.Close savechanges:=False
    objExcel.Quit
    Set objExcel = Nothing
    Set objWorkbook = Nothing
    Set objWorksheet = Nothing
 End If
 Exit Sub
End Sub

Well, any ideas??
0
Comment
Question by:seeshelle
6 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 50 total points
ID: 2624827
Try setting rngExcel to nothing as well.  This could be holding it open.
0
 

Author Comment

by:seeshelle
ID: 2625100
I'll give it a try, also I wanted to add that it just leaves one instance open, not once for each time you hit the save button, if this helps.
0
 
LVL 1

Expert Comment

by:georgeman
ID: 2625949
Did try to use instead:
    objExcel.Quit
    Set objExcel = Nothing
    Set objWorkbook = Nothing
    Set objWorksheet = Nothing
 next code
    Set objWorkbook = Nothing
    Set objWorksheet = Nothing
    objExcel.Quit
    Set objExcel = Nothing
 ?
What I mean maybe more properly to use line in which you're closing Excel object on the last place.
Regards
George
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:pclement
ID: 2626595
Yeah, seems to be a problem with Excel not terminating properly. I use the following clean-up code:

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal
lpClassName As Any, ByVal lpWindowName As Any) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal
hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As
Long

Private Const WM_QUIT = &H12

Sub TerminateExcel()

Dim lpClassName As String
Dim iHwnd As Long
Dim iReturn As Long

lpClassName = "XLMain"
iHwnd = FindWindow(lpClassName, 0&)

If iHwnd Then
    iReturn = PostMessage(iHwnd, WM_QUIT, 0&, 0&)
End If

End Sub
0
 

Author Comment

by:seeshelle
ID: 2628901
That was really great! It worked wonderfully! Who'd ever thought something so simple could work so well. Thanks!!
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2629003
Not a problem - I've noticed in the past that 'unreleased' objects can leave the programs hanging open in the background.  It's the first thing to look for, IMO.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

740 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