Remove Excel task from Task manager after running from Access using VBA

Question

I will now take the opportunity to ask here, I have really tried a lot of different way, but it seems that I am not able to be able to close the Excel task in task-manger, It hangs until I close Access completely, annoying, because I can not run two different jobs using Excel from Access. Second job will give me errors.

I have made some comments to where I still is able to get rid of Excel. The purpose for the code is to run some query's and export data to excel and then lock the excel sheet so users only can fill in answers to the data.

Code:

Private Sub Command65_Click()
Dim r As Double
'On Error GoTo Error_Handler
Dim objExcel As Excel.Application
Dim objWorkbook As Workbook
Dim objWorksheet As Worksheet
Dim dbs As DAO.Database
Dim rSt As DAO.Recordset
Set dbs = CurrentDb
Set rSt = CurrentDb.OpenRecordset("qry_VC_Confirmation")

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

'objExcel.Quit  ' at this point it still works to close again
'Set objExcel = Nothing ' at this point it will remove from task manager

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

'Set objWorkbook = Nothing ' can close still at this stage
'Set objWorksheet = Nothing ' can close still at this stage
'objExcel.Quit  ' at this point it still works to close again ?
'Set objExcel = Nothing ' at this point it still will not remove from task manager

iFld = 0
irow = 1
For icol = 1 To (rSt.Fields.count)
    objWorksheet.Cells(irow, icol) = rSt.Fields(iFld).Name
    objWorksheet.Cells(irow, icol).Interior.ColorIndex = 1
    objWorksheet.Cells(irow, icol).Font.ColorIndex = 2
    objWorksheet.Cells(irow, icol).Font.Bold = True
    iFld = iFld + 1
Next

'Set objWorkbook = Nothing '
'Set objWorksheet = Nothing '
'objExcel.Quit  ' at this point it still works to close Excel again ?
'Set objExcel = Nothing ' at this point it will still remove from task manager

irow = 2
If Not rSt.BOF Then rSt.MoveFirst
Do Until rSt.EOF
    iFld = 0
    lRecords = lRecords + 1
    For icol = 1 To (rSt.Fields.count)
        objWorksheet.Cells(irow, icol) = rSt.Fields(iFld)
        iFld = iFld + 1
        Next
        irow = irow + 1
        rSt.MoveNext
Loop
r = irow - 1
Columns("A:F").EntireColumn.AutoFit

ActiveSheet.Protection.AllowEditRanges.Add Title:="Unprotected", Range:=Range("F2:F" & r)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="secret"

objWorkbook.SaveAs ("C:\Dropbox\VC_Confirmation.xlsx")

ExitSub:
Set objWorkbook = Nothing '
Set objWorksheet = Nothing '
objExcel.Quit  ' at this point it still works to close excel again ?
Set objExcel = Nothing ' at this point it will **NOT** remove from task manager

Exit Sub

Error_Handler:
MsgBox Error$
Resume ExitSub

End Sub

Maybe if you put `objWorkbook.Close False` after the `ExitSub:` — simpLE MAn, Mar 19 '14 at 18:05
> Close the workbook, then Quit Excel, then set your objects to Nothing — Alan Waage, Mar 19 '14 at 18:29
>>Alan, if you mean objExcel.quit I already tried this. I most cases I get "object variable or with block variable not set" the strange thing is that is it somehow the last For loop where I insert all values the causes this all to happen. — AK-Denmark, Mar 19 '14 at 19:39
Please add an "@" in front of the user's name your comment is aimed to so he or she will be notified. You can click help while commenting to see more info. — simpLE MAn, Mar 19 '14 at 19:46
If I open up the code window after the function has executed and just hit the "stop" Button (=Reset) then it disappears ( so what do I miss in my code ?) — AK-Denmark, Mar 19 '14 at 19:54
Yes I saw your code, but you have to do that before you set the objects to nothing. Workbook.Close, then Excel.Quit, then worksheet = nothing, workbook = Nothing, and Excel = Nothing. I have done this multiple times myself and it works. Also if you are not pushing a save out, make sure you tell it to close without saving, or it will stick trying to ask the user if they want to save. (Application.DisplayAlerts = False) — Alan Waage, Mar 19 '14 at 19:57
If you put `MsgBox "TEST"` right after `ExitSub:` does it get triggered? — simpLE MAn, Mar 19 '14 at 20:00
@AlanWaage okay I now tried this:objWorkbook.SaveAs ("C:\Dropbox\VC_Confirmation.xlsx") ExitSub: objWorkbook.Close False objExcel.Quit Set objWorksheet = Nothing Set objWorkbook = Nothing Set objExcel = Nothing — AK-Denmark, Mar 19 '14 at 20:46
@AlanWaage, ups! I am not good at this, I cannot hit "Return" well the above still do not work, only solution is to reopen Code window and hit the square button (=reset) I am not sure what you mean with the "pushing a save out " ? — AK-Denmark, Mar 19 '14 at 20:50
@AK-Denmark 'pushing a save out' means telling Excel to save the file. — Alan Waage, Mar 20 '14 at 13:48
Is there any other way to do this, It will absolutely not work ? :-( — AK-Denmark, Mar 25 '14 at 20:28
@AK-Denmark Are you absolutely positive that the instance left open is not one that was left by terminating your code early while debugging? — RubberDuck, Apr 28 '14 at 20:01
I bet you this is the problem - "Columns("A:F").EntireColumn.AutoFit " You need to fully qualify all your references or it will cause excel to hang. a single unqualified "Range(A1)" or "Activesheet" will tick up a counter linked to that comobject and there wont be a way to release it without using the task manager — Acantud, Apr 28 '14 at 21:10

RubberDuck · Answer 1 · 2014-08-04T21:42:26.880

In the comments you mentioned that you had reset your code (i.e. pressed the stop button). This means that the portion of your code that kills excel did not run, thus leaving an open session of excel. There is a small (possibly semantic) issue with your code, but I don't believe that's what was causing your issue. Regardless, you should properly shut down the application like this.

ExitSub:
    If Not objWorksheet Is Nothing Then
        set objWorksheet = Nothing
    End If
    ' You have to check for the workbook's existence before 
    '    you try to close something that isn't there. This avoids runtime errors.
    '    Since your error handler points you back here, this code always runs, so
    '    The workbook might not be open.
    If Not objWorkbook Is Nothing Then
        objWorkbook.close
        Set objWorkbook = Nothing
    End If
    ' Same goes for quitting the application
    If Not objExcel Is Nothing Then
        objExcel.Quit
        Set objExcel = Nothing
    End If

    Exit Sub
Error_Handler:
    ' error handling code here
     Resume ExitSub
End Sub

Thanks so much for adding this suggestion, I will try it out ASAP. — AK-Denmark, Apr 30 '14 at 17:20

score 1 · Answer 2 · answered Apr 28 '14 at 21:13

Columns("A:F").EntireColumn.AutoFit

Adding as an answer just in case. Fully qualify this with the worksheet name and try again. This same problem was a huge bother to me too. You have to qualify 100% of your references, no matter what. Also, be super careful about using With statements on ranges, worksheets etc. So change it to ObjWorksheet.Columns("A:F")... instead

Remove Excel task from Task manager after running from Access using VBA

2 Answers2

Linked

Related