Solved

Excel isn't closing!

Posted on 2000-03-16
6
164 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now