Solved

Excel isn't closing!

Posted on 2000-03-16
6
179 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:
Brendt Hess 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
Industry Leaders: 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:Brendt Hess
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…
Suggested Courses
Course of the Month9 days, 16 hours left to enroll

623 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