如何从MS Access VBA中删除孤立的MS Excel进程

Question

我正在将MS Access查询导出到模板中，进行一些格式化，然后将模板保存为新名称。当这一切完成后，我有一个孤立的MS Excel进程，当再次调用函数时，它会干扰。我认为这要么是我如何使用范围的问题，要么是我最后清理的问题。

此外，我是一个程序员新手，所以如果有人有任何技巧和窍门，我可以利用它们来做得更好，我总是乐于接受。

Andre评论后更新代码Rory评论后更新代码

Public Function OpenOrders(strSupplier As String)
'Excel file variables
Dim xlapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim xlsLRow As Long
Dim xlsLCol As Long
'Access variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
'Set up access objects
strSQL = "SELECT * FROM qryOpenOrderReport WHERE [Supplier Cd] = '" & strSupplier & "';"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
'Set up excel connection
Set xlapp = CreateObject("Excel.Application")
Set wb = xlapp.Workbooks.Open(Application.CurrentProject.Path & "Open Order Template.xlsx")
Set ws = wb.Worksheets(1)
xlapp.Visible = True
'Make sure the form is clear
xlsLRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).row
xlsLCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
ws.Range("A2", ws.Cells(xlsLRow, xlsLCol)).ClearContents
'Copy recordset to worksheet
ws.Cells(2, 1).CopyFromRecordset rs
rs.Close
'Copy formats down and autofit
xlsLRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).row
xlsLCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
xlapp.CutCopyMode = False
ws.Range(ws.Cells(2, 1), ws.Cells(2, xlsLCol)).Copy
ws.Range(ws.Cells(3, 1), ws.Cells(xlsLRow, xlsLCol)).PasteSpecial (xlPasteFormats)
ws.UsedRange.Columns.AutoFit
'Clean up
xlapp.DisplayAlerts = False
Set ws = Nothing
wb.SaveAs Application.CurrentProject.Path & "Open Orders" & strSupplier & ".xlsx"
wb.Close True
Set wb = Nothing
xlapp.Quit
Set xlapp = Nothing
End Function

Answer 1

所有的Cells调用都需要更改为ws.Cells。这就是导致孤立进程的原因。

Answer 2

我的猜测是，在进行清理时，您仍然有一个对ws的活动引用，这将阻止Excel退出。

我建议按以下顺序进行：

'Clean up
xlapp.DisplayAlerts = False
Set ws = Nothing
wb.Close True, strSupplier
Set wb = Nothing
xlapp.Quit
Set xlapp = Nothing

---

ws.Range("A2", "XFD1048576").ClearContents似乎有点激进：）-可以使用.UsedRange。

---

附加说明：

打开记录集后，您永远不会处于rs.EOF为False，但rs.BOF为True的情况。因此没有必要对rs.BOF进行测试。

将循环更改为Do While后，If Not (rs.EOF And rs.BOF) Then变得多余：

Set rs = db.OpenRecordset(strSQL)
Do While Not rs.EOF 
    ' ...
    rs.MoveNext
Loop

Answer 3

您必须非常具体地处理Excel的对象，以相反的顺序打开和关闭它们。这是一个有效的骨架：

 Dim xls     As Excel.Application
 Dim wkb     As Excel.Workbook
 Dim wks     As Excel.Worksheet
 Dim rng     As Excel.Range
 Set xls = New Excel.Application
 Set wkb = xls.Workbooks.Open("c:testworkbook1.xlsx")
 Set wks = wkb.Worksheets(1)
 Set rng = wks.<somerange>  ' Cells or whatever.
 ' Do stuff.
 ' Clean up.
 Set rng = Nothing
 wks.Name = "My New Name"
 wkb.Close True
 Set wks = Nothing
 Set wkb = Nothing
 xls.Quit
 Set xls = Nothing

永远不要使用wkb。工作表的工作表。