ACCESS数据库导入EXCEL的问题,我写的速度太慢了,大家帮我看看怎样能提高速度,谢谢大家
ACCESS数据库导入EXCEL的问题,我写的速度太慢了,大家帮我看看怎样能提高速度,谢谢大家.
代码如下,
conn.Open
Dim wy As Long
Dim h,a,b,j As Integer
h = 0
wy = 8
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlApp = CreateObject( "Excel.Application ")
xlApp.Visible = False
xlApp.DisplayAlerts = False
Dim Exstr As String '查询Excel表路径
Exstr = App.Path
If Right(Exstr, 1) <> "\ " Then
Exstr = Exstr + "\ "
End If
Exstr = Exstr & "\打印模版.xls "
Set xlBook = xlApp.Workbooks.Open(Exstr)
Set xlSheet = xlBook.Worksheets(1)
j = 8 '打印的第一个位置
For b = 1 To dpPCount Step 1 '循环N次,要导出多个表, dpPCount这个是变量
rst.Open "select * from worktable " & b & " ", conn, adOpenDynamic, adLockOptimistic '查询表
Do Until rst.EOF '这个地方开始把数据库中的数据导入到excel中,不过速度有点.... :(
For a = 1 To rst.Fields.Count
xlSheet.Cells(j, a) = rst.Fields(a - 1)
Next a
rst.MoveNext
j = j + 1
Loop
wy = wy + 47
j = wy '下次打印的位置.....
Next b
xlApp.Visible = True
xlApp.ActiveSheet.PrintPreview '打印预览
Set xlApp = Nothing
conn.Close
[解决办法]
Do Until rst.EOF
For a = 1 To rst.Fields.Count
xlSheet.Cells(j, a) = rst.Fields(a - 1)
Next a
rst.MoveNext
j = j + 1
Loop
循环嵌套循环，能不慢吗？如果记录是一万条，字段五十个，就要运算和赋值五十万次，还有判断。CPU自杀的心情都有了！
向你推荐 "DTE报表工具 ",我用过几回.速度还行
[解决办法]
dim DataArr () as string
redim DataArr(rst.count,rst.fields.count)
Do Until rst.EOF
For a = 1 To rst.Fields.Count
DataArr(j, a) = rst.Fields(a - 1)
Next a
rst.MoveNext
j = j + 1
Loop
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(rst.count,rst.fields.count)).Value =DataArr()
代碼大概是這樣，沒有進一步。你耐心測試一下
思路是這樣的
先將數據賦值給一個數組DataArr()，然后將數組的內容一次賦值給excel。速度奇快
