We help IT Professionals succeed at work.

excel does not close properly in my task manager after closing it

1,681 Views
Last Modified: 2013-12-03
To all VB experts,

(i donate an EXTRA 500 points to a correct answer  THAT REALLY WORKS)

my excel does not close entirely after doing xl.application.quit
i also released it from the memory by adding:
set xl = nothing
set wb = nothing

it still does not work

this is my code (i'm belgian so don't mention the dutch comment)



If Main.rs.State = adStateOpen Then Main.rs.Close
Main.rs.CursorLocation = adUseClient
Main.rs.Open SQL, Main.conn


rijteller = 14
If Not Main.rs.EOF Or Not Main.rs.BOF Then


    '########################################
    '     EXCEL ACTIVEREN INDIEN NODIG
    '########################################

    Dim xl As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

    Set xl = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set xl = CreateObject("Excel.Application")
    End If

    Err.Clear
     
    Set wb = xl.Workbooks.Add
    Set ws = wb.ActiveSheet
    xl.Visible = True


    Columns("A:A").ColumnWidth = 25
    Columns("B:B").ColumnWidth = 30

    Range("A1:B1").Select
    Selection.MergeCells = True
    Selection.Font.Bold = True
    ws.Range("A1").Value = "VERKOOPSTATISTIEKEN" & Chr(10) & "MARCO BETH - BE 417.288.852"
    ws.Range("A2").Value = Date & " " & Time

    ws.Range("A4").Value = "Document datum:"
    ws.Range("B4").Value = "van " & van & " tot " & tot


    ws.Range("A12").Value = "KLANTNAAM"
    ws.Range("B12").Value = "HOEVEELHEID"
    Range("B12").Select
    Selection.HorizontalAlign = xlRight
    Range("A12:B12").Select
    Selection.Font.Bold = True


    Dim tmpaantal As Single
        Do While Not Main.rs.EOF
       
        If Main.rs("klantnaam") <> tmpklant And tmpklant <> "" Then
            Range("A" & rijteller & ":B" & rijteller).Select
            Selection.Borders(xlEdgeBottom).Weight = xlThin
            tmpaantal = 0
            rijteller = rijteller + 1
        End If
       
        If Main.rs("aantal") <> "" Then
            tmpaantal = tmpaantal + CSng(Main.rs("aantal"))
            totaantal = totaantal + CSng(Main.rs("aantal"))
        End If
       
        adres = Main.rs("klantnaam") & Chr(10) & Main.rs("adres") & Chr(10)
        adres = adres & Main.rs("postcode") & " " & Main.rs("woonplaats")
        ws.Range("A" & rijteller).Value = adres
        ws.Range("B" & rijteller).Value = tmpaantal
       
        tmpklant = Main.rs("klantnaam")
       
       
        Main.rs.MoveNext
    Loop
    rijteller = rijteller + 2
    ws.Range("A" & rijteller).Value = "ALGEMEEN TOTAAL"
    ws.Range("B" & rijteller).Value = totaantal


    '########################################
    '     OPMAAK VAN HET WERKBLAD
    '########################################

    Range("A1:B" & rijteller).Select
    Selection.Font.Size = 8
    Selection.VerticalAlignment = xlTop
   
    Range("B4:B" & rijteller).Select
    Selection.NumberFormat = "0.00"
   
    'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True



    '########################################
    '     AFSLUITEN VAN EXCELOBJECT
    '########################################

    'xl.ActiveWorkbook.Close
   
    wb.Close
    'SendKeys ("n")
    xl.Application.Quit
    Set ws = Nothing
    Set wb = Nothing
    Set xl = Nothing
   

Else
    MsgBox ("Er werden geen resultaten gevonden.")
End If
Comment
Watch Question

Freelance
Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
ok, your answer really helped me Bruintje. I'll accept, how do I donate the extra 500 points?
Brian MulderFreelance
Top Expert 2006

Commented:
not needed, if it helped then i'm happy too :)

Commented:
I wrote this piece of software five yers ago and it run fine,i hope it will help you (detect excel ,is not usedin this app. but may be use to avoid to close excel if the user uses it or an other task)
Dim MYXL As Object
Dim xlnotrunning As Boolean
Dim xlBook As Excel.Workbook
Dim xlsheet As Excel.Sheets

On Error Resume Next
xlnotrunning = False
MsgBox ("suivant la puissance de votre machine, vous aurez un message excel ne repond pas , taper reessayer ,puis excel demarre ,laissez faire !")
Set MYXL = GetObject(, "Excel.sheet")
If Err.Number <> 0 Then xlnotrunning = True
Err.Clear
'detectexcel
Set MYXL = GetObject(App.Path & "\11.xls")
MYXL.Application.Visible = True
MYXL.Parent.Windows(1).Visible = True
'Me.Visible = False
 For i = 1 To 20
   
     
    MYXL.ActiveSheet.Cells(1, i).Value = lblLabels(i + 4).Caption
    MYXL.ActiveSheet.Cells(2, i).Value = Val(txtFields(i + 4).Text)
   
  Next
 
  For i = 1 To 15
  result(i - 1).Visible = True
 
  MYXL.ActiveSheet.Cells(5, i).Value = lblLabels(i + 24).Caption
  result(i - 1).Text = MYXL.ActiveSheet.Cells(6, i).Value
 
  Next
  Cmdchrg.Visible = False
  CDMVALID.Visible = True
  CMDANNUL.Visible = True
 
  MYXL.SaveAs FileName:="c:\windows\temp\11.xls"
 

 MYXL.Application.Quit
Set MYXL = Nothing
Kill ("c:\windows\temp\11.xls")
End Sub

Sub detectexcel()
Const WM_user = 1024
Dim hwnd As Long
hwnd = FindWindow("XLMAIN", 0)
If hwnd = 0 Then
Exit Sub
Else
SendMessage hwnd, WM_user + 18, 0, 0
End If
End Sub

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.