帮我优化以下代码,不使用ADO ,然后每次导出不同的10000条记录至新的EXECL文件中:Dim strSQL As String Dim strFileName As String Dim i As Long Dim batchSize As Long '每批次导出的记录数 Dim batchCount As Long '批次计数器 Dim exportCount As Long '导出计数器 '设置导出文件路径和文件名 strFileName = "C:\Users\icd\Desktop\流水分割\" batchSize = 10000 '每批次导出的记录数 DoCmd.SetWarnings False '暂时关闭Access的警告信息 DoCmd.RunSQL "SELECT 导出数据.* INTO ExportData FROM 导出数据" '将表导入临时表ExportData DoCmd.SetWarnings True '重新开启Access的警告信息 Set rs = CurrentDb.OpenRecordset("SELECT * FROM ExportData", dbOpenSnapshot) '打开临时表ExportData '循环导出数据 Do While Not rs.EOF If exportCount Mod batchSize = 0 Then '每批次导出到一个新的文件 If exportCount <> 0 Then DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ExportData", strFileName & Format(batchCount, "000000") & ".xlsx", True End If batchCount = batchCount + 1 End If exportCount = exportCount + 1 rs.MoveNext Loop '导出最后一份文件 If exportCount Mod batchSize <> 0 Then DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ExportData", strFileName & Format(batchCount, "000000") & ".xlsx", True End If rs.Close Set rs = Nothing DoCmd.SetWarnings False '暂时关闭Access的警告信息 DoCmd.RunSQL "DROP TABLE ExportData" '删除临时表ExportData DoCmd.RunSQL "DELETE 导出数据.* FROM 导出数据" '删除导出数据内容 DoCmd.SetWarnings True '重新开启Access的警告信息
时间: 2024-04-20 21:27:33 浏览: 82
VB中的ADO对象编程.doc
以下是优化后的代码,使用了Recordset来处理数据,同时每次导出10000条记录到新的Excel文件中:
Dim strSQL As String
Dim strFileName As String
Dim i As Long
Dim batchSize As Long '每批次导出的记录数
Dim batchCount As Long '批次计数器
Dim exportCount As Long '导出计数器
Dim rs As Recordset
'设置导出文件路径和文件名
strFileName = "C:\Users\icd\Desktop\流水分割\"
batchSize = 10000 '每批次导出的记录数
'将表导入临时表ExportData
DoCmd.SetWarnings False '暂时关闭Access的警告信息
DoCmd.RunSQL "SELECT 导出数据.* INTO ExportData FROM 导出数据"
DoCmd.SetWarnings True '重新开启Access的警告信息
'SQL语句,使用Recordset来处理数据
strSQL = "SELECT * FROM ExportData"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
'循环导出数据
Do While Not rs.EOF
If exportCount Mod batchSize = 0 Then
'每批次导出到一个新的文件
If exportCount <> 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ExportData", strFileName & Format(batchCount, "000000") & ".xlsx", True
End If
batchCount = batchCount + 1
End If
exportCount = exportCount + 1
rs.MoveNext
Loop
'导出最后一份文件
If exportCount Mod batchSize <> 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ExportData", strFileName & Format(batchCount, "000000") & ".xlsx", True
End If
'关闭Recordset和删除临时表ExportData
rs.Close
Set rs = Nothing
DoCmd.SetWarnings False '暂时关闭Access的警告信息
DoCmd.RunSQL "DROP TABLE ExportData" '删除临时表ExportData
DoCmd.RunSQL "DELETE 导出数据.* FROM 导出数据" '删除导出数据内容
DoCmd.SetWarnings True '重新开启Access的警告信息
阅读全文