Question

在EXCEL中使用VBA连接数据库查询，每运行一次，进程就多一个，请问什么原因，如何改善

Sub test()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(2)
Dim Fieldlen() As Integer
Dim Irowcount, Icolcount As Integer
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim path As String
path = "C:\Users\declanjun\Desktop\excel-db.accdb"
Dim connstr As String
connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";"
' Set conn = CreateObject("ADODB.Connection")
conn.Open connstr
Dim sql As String
sql = "select * from epp" 'SQL语句,决定从数据库中取哪些数据,填入rs记录集
rs.Open sql, conn, 1, 1

With rs
If .RecordCount < 1 Then
MsgBox ("no result!")
Exit Sub
End If
Irowcount = .RecordCount
Icolcount = .Fields.Count
rs.MoveFirst
ReDim Fieldlen(Icolcount) As Integer
For Irow = 1 To Irowcount + 1
For Icol = 1 To Icolcount

Select Case Irow
Case 1 '在Excel中的第一行加标题
xlSheet.Cells(Irow, Icol).Value = RTrim(.Fields(Icol - 1).Name)
Case 2 '将数组FIELDLEN()存为第一条记录的字段长
If IsNull(.Fields(Icol - 1)) = True Then
Fieldlen(Icol) = LenB(RTrim(.Fields(Icol - 1).Name))
Else
aa = RTrim(.Fields(Icol - 1))
Fieldlen(Icol) = LenB(aa)
End If
xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
xlSheet.Cells(Irow, Icol).Value = RTrim(.Fields(Icol - 1))
Case Else
Fieldlen1 = LenB(.Fields(Icol - 1))
If Fieldlen(Icol) < Fieldlen1 Then
xlSheet.Columns(Icol).ColumnWidth = Fieldlen1
Fieldlen(Icol) = Fieldlen1
Else
xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
End If
xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1)
End Select
Next
If Irow <> 1 Then
If Not .EOF Then .MoveNext
End If
Next
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Name = "黑体"
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Bold = True
.Range(.Cells(1, 1), .Cells(Irow, Icol - 1)).Borders.LineStyle = xlContinuous
End With
xlApp.Visible = True
xlBook.Save
Set xlApp = Nothing
End With
End Sub
试了下好像还是会增加，还没搞懂怎么回事，时间也少，这问题再挂几天看看

Answer 1

1.首先你没有关闭Excel.Application，你只是xlApp = Nothing
，让对象失去引用。应该让xlApp.quit并在执行这行代码前关闭xlApp打开的所有Excel文件。
2.不建议你这样CreateObject("Excel.Application") 。因为Excel进程不是那么容易关掉的，你可以直接用执行这段代码的Excel，就是直接用Application。
也就是Set xlBook = xlApp.Workbooks.Add
改成Set xlBook = Application.Workbooks.Add

Answer 2

最后这一块
因为你没有关闭新建的工作簿，肯定进程会一直增加的
xlApp.Visible = True
xlBook.Save
'加入2条
xlbook.close ’关闭工作簿
Set xlbook = Nothing '清空对象
Set xlApp = Nothing

End With
End Sub