• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Excel isn't closing!

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
seeshelle
Asked:
seeshelle
1 Solution
 
Brendt HessSenior DBACommented:
Try setting rngExcel to nothing as well.  This could be holding it open.
0
 
seeshelleAuthor Commented:
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
 
georgemanCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
pclementCommented:
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
 
seeshelleAuthor Commented:
That was really great! It worked wonderfully! Who'd ever thought something so simple could work so well. Thanks!!
0
 
Brendt HessSenior DBACommented:
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
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now