Solved

Excel isn't closing!

Posted on 2000-03-16
6
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: 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

Technology Partners: 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

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

752 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