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。速度奇快