优化Excel单元格写入速度：Access数据填充技术问询

核心问题本质 #

你当前逐单元格写入的方式耗时极长，根源是每次与Excel对象模型交互都会产生额外开销，10000行的循环会累积大量不必要的交互成本。

优化方案 #

1. 内存数组批量修改后一次性写入 #

将需要操作的Excel区域先读入内存数组，在数组内完成所有填充和检查逻辑，最后一次性写回Excel，彻底减少对象交互次数：

' 1. 定义目标数据区域（假设数据从第2行到10001行，共10000行75列）
Dim dataRange As Range
Set dataRange = objWS.Range(objWS.Cells(2, 1), objWS.Cells(10001, 75))

' 2. 将区域数据加载到内存数组
Dim excelArray As Variant
excelArray = dataRange.Value

' 3. 遍历数组，完成数据填充与已填充检查
For Row = 1 To UBound(excelArray, 1)
    ' 从首列获取查询关键字（数组行索引对应Excel行+1）
    Dim key As String
    key = excelArray(Row, 1)
    
    ' 执行SQL查询获取目标数据（保留你原有查询逻辑）
    ' ...（此处获取Array_Columns数据）
    
    ' 在内存数组中填充目标列，同时检查是否已填充
    For z = LBound(Array_Destination) To UBound(Array_Destination)
        Dim targetCol As Integer
        targetCol = 13 + Array_Destination(z)
        ' 根据需求调整检查条件，比如判断是否为空
        If IsEmpty(excelArray(Row, targetCol)) Then
            excelArray(Row, targetCol) = Array_Columns(0, z)
        End If
    Next z
Next Row

' 4. 将修改后的数组一次性写回Excel
dataRange.Value = excelArray

2. 关闭Excel后台不必要的功能 #

操作前关闭屏幕刷新、自动计算和事件触发，避免资源浪费：

' 操作前禁用
objWS.Application.ScreenUpdating = False
objWS.Application.Calculation = xlCalculationManual
objWS.Application.EnableEvents = False

' 执行你的数据填充逻辑（如上述数组操作）
' ...

' 操作后恢复
objWS.Application.ScreenUpdating = True
objWS.Application.Calculation = xlCalculationAutomatic
objWS.Application.EnableEvents = True

3. 批量优化SQL查询 #

如果当前是逐行执行一次SQL，建议收集所有查询关键字后批量查询，减少与Access的交互次数：

' 收集所有首列关键字
Dim keyList As String
For Row = 2 To 10001
    keyList = keyList & "'" & objWS.Cells(Row, 1).Value & "',"
Next Row
keyList = Left(keyList, Len(keyList) - 1) ' 移除末尾多余逗号

' 批量查询所有目标数据
Dim rs As Recordset
Set rs = objAccess.OpenRecordset("SELECT * FROM YourTable WHERE YourKeyField IN (" & keyList & ")")

' 将查询结果存入字典，方便快速匹配
Dim dataDict As Object
Set dataDict = CreateObject("Scripting.Dictionary")
Do While Not rs.EOF
    dataDict(rs("YourKeyField").Value) = rs.Fields ' 存储该行所有字段
    rs.MoveNext
Loop

' 后续遍历Excel行时，直接从字典取数据，无需逐行查询

关键提示 #

• 内存数组操作几乎无交互开销，相比逐单元格写入速度可提升几十至上百倍
• 单元格已填充检查在内存数组内完成，完全不影响效率
• 批量查询能大幅减少与Access的连接交互，进一步压缩整体耗时

内容的提问来源于stack exchange，提问作者Hendrik