问题描述
现在我趁机问在这里,我真的尝试了很多不同的方式,但似乎我无法能够关闭在任务管理器中的Excel任务,它挂起,直到我结束访问彻底,恼人的,因为我不能运行使用Excel从接入两个不同的工作。第二工作可以给我的错误。
我已经取得了一些意见,我仍然无法摆脱的Excel。为code的目的是运行某些查询的和将数据导出到Excel,然后锁定Excel工作表中,因此用户只能在回答数据填写。
code:
私人小组Command65_Click()
昏暗为r双
对错误转到Error_Handler
昏暗objExcel作为Excel.Application
昏暗objWorkbook作为工作簿
昏暗objWorksheet作为工作表
昏暗的星展银行作为DAO.DATABASE
昏暗RST作为DAO.Recordset
设置DBS = CurrentDb
设置RST = CurrentDb.OpenRecordset(qry_VC_Confirmation)
设置objExcel =的CreateObject(Excel.Application)
objExcel.Visible = TRUE
objExcel.Quit在这一点上它仍然工程再次关闭
在这一点上设置objExcel =什么',它会删除任务管理器
设置objWorkbook = objExcel.Workbooks.Add
设置objWorksheet = objWorkbook.Worksheets(1)
设置objWorkbook =什么'可以关闭现阶段仍
设置objWorksheet =什么'可以关闭现阶段仍
objExcel.Quit在这一点上它仍然工程再次关闭?
在这一点上设置objExcel =什么'它仍然不会从任务管理器中删除
iFld = 0
irow = 1
对于ICOL = 1到(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
下一个
设置objWorkbook =什么'
设置objWorksheet =什么'
objExcel.Quit在这一点上它仍然有效,再次关闭Excel?
在这一点上设置objExcel =什么'它仍然会从任务管理器中删除
irow = 2
如果没有rSt.BOF然后rSt.MoveFirst
做直到rSt.EOF
iFld = 0
lRecords = lRecords + 1
对于ICOL = 1到(rSt.Fields.count)
objWorksheet.Cells(irow,ICOL)= rSt.Fields(iFld)
iFld = iFld + 1
下一个
irow = irow + 1
rSt.MoveNext
循环
R = irow - 1
列(A:F)EntireColumn.AutoFit。
ActiveSheet.Protection.AllowEditRanges.Add标题:=无保护,范围:=范围(F2:F和放大器; R)
个ActiveSheet.Protect DrawingObjects:= TRUE,内容:= TRUE,场景:= TRUE,密码:=秘密
objWorkbook.SaveAs(C:\ Dropbox的\ VC_Confirmation.xlsx)
ExitSub:
设置objWorkbook =什么'
设置objWorksheet =什么'
objExcel.Quit在这一点上它仍然有效,再次关闭Excel?
设置objExcel =什么'在这一点上,它会** **不从任务管理器中删除
退出小组
Error_Handler:
MSGBOX错误$
简历ExitSub
结束小组
在你提到的意见,你有重置code(即pressed停止按钮)。这意味着你的code中的部分杀害的Excel没有运行,从而退出Excel的一个公开会议。有一个小的(可能是语义的)问题,您的code,但我不认为这是什么造成您的问题。无论如何,你应该正确地关闭这样的应用程序。
ExitSub:
如果没有objWorksheet是没有那么
设置objWorksheet =无
结束如果
你必须之前检查工作簿的存在
您尝试关闭一些不存在。这避免了运行时错误。
由于您的错误处理程序指向你回到这里,这code总是运行,所以
该工作簿可能无法打开。
如果没有objWorkbook是没有那么
objWorkbook.close
设置objWorkbook =什么
结束如果
同去的退出申请
如果没有objExcel是没有那么
objExcel.Quit
设置objExcel =什么
结束如果
退出小组
Error_Handler:
错误处理code在这里
简历ExitSub
结束小组
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
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任务的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!