使用 VBA 从 Access 运行后从任务管理器中删除 Excel 任务 [英] Remove Excel task from Task manager after running from Access using VBA

问题描述

我现在借此机会在这里问一下，我确实尝试了很多不同的方法，但似乎无法关闭任务管理器中的Excel任务，直到我关闭Access才会挂起完全，烦人，因为我无法使用 Access 中的 Excel 运行两个不同的作业.第二份工作会给我带来错误.

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.

我已经对我仍然能够摆脱 Excel 的地方发表了一些评论.代码的目的是运行一些查询并将数据导出到excel，然后锁定excel表，以便用户只能填写数据的答案.

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.

代码:

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:DropboxVC_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

推荐答案

在您提到的评论中，您已重置代码(即按下了停止按钮).这意味着杀死 excel 的代码部分没有运行，因此留下了 excel 的开放会话.您的代码存在一个小(可能是语义)问题，但我认为这不是导致您出现问题的原因.无论如何，您应该像这样正确关闭应用程序.

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

这篇关于使用 VBA 从 Access 运行后从任务管理器中删除 Excel 任务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！