下面1，2是execl的一次性读取和保存示例，供调试和参考。另外数据量小的读取也可以直接用Excel.Application
你在比较时用内存数组，结果也用内存数组，之后保存。
1、execl读到内存数组txt1【直接调用函数，我的驱动是对xls格式的execl】
Public Sub Load_Execl(ByVal execl_name As String, ByVal sheet_name, txt1)
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long, j As Long
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & execl_name & ";extended properties= 'Excel 8.0;HDR=YES;IMEX=1';"
rs.Open "select * from [" + sheet_name + "$]", cn, adOpenKeyset, adLockOptimistic
ReDim txt1(rs.RecordCount, rs.Fields.Count)
For i = 1 To rs.Fields.Count: txt1(0, i) = rs.Fields(i - 1).Name: Next i '读第一行【首行当标题了】
For i = 1 To rs.RecordCount '读其余行
For j = 1 To rs.Fields.Count: txt1(i, j) = IIf(Not IsNull(rs.Fields(j - 1)), rs.Fields(j - 1), ""): Next j
rs.MoveNext
'此处如果增加显示读取行数idea语句，增加doevents
Next i
Set rs = Nothing
Set cn = Nothing
End Sub
2、txt2保存到execl
Public Sub Save_Execl(txt2)
Dim nRows As Long, nColumns As Long
Set NewXls = CreateObject("Excel.Application") '创建excel应用程序
NewXls.SheetsInNewWorkbook = 1
Set newbook = NewXls.Workbooks.Add '创建工作簿
Set NewSheet = newbook.Worksheets(1) '创建工作表
NewXls.DisplayAlerts = False '
nRows = UBound(txt1, 1)
nColumns = UBound(txt1, 2)
'导出到Excel中
Set objRange = NewSheet.Range(NewSheet.Cells(1, 1), NewSheet.Cells(nRows, nColumns))
objRange.Value = txt2
NewXls.Workbooks(1).Worksheets(1).Name = "D1H"
newbook.SaveAs FileName:="execl名"
newbook.Close
Set newbook = Nothing
Set NewXls = Nothing
end sub
[此贴子已经被作者于2018-3-14 10:01编辑过]
