|
以下程序是从access数据库中读取数据,并填入excel,数据量有80000*28个,需要耗时45分钟左右,求各位大神,有没有高速方案?
Sub 实际下载() '实际费用数据下载
'量声明
Dim DB As Database '定义数据库
Dim DBOpen As Recordset '定义数据库表打开语句
Dim datatsheet As Worksheet
Dim shezhisheet As Worksheet
Dim DBpath As String '定义数据库路径
Dim lastrow As Long '定义最后行
'量赋值
Set datasheet = ThisWorkbook.Sheets("实际")
Set shezhisheet = ThisWorkbook.Sheets("设置")
DBpath = shezhisheet.Cells(2, 1) '设置数据库路径
Set DB = OpenDatabase(DBpath & shezhisheet.Cells(2, 2) & ".accdb") '设置数据库打开方式
Set DBOpen = DB.OpenRecordset(Name:="实际费用合并", Type:=dbOpenDynaset) '打开数据库表语句
lastrow = datasheet.[a60000].End(xlUp).Row '表最后一行
'函数执行
'删除sheet内容,并读取数据
If lastrow > 3 Then
datasheet.Rows("3:" & lastrow).ClearContents '清空读取页
End If
Mychecksql = "select a.* from [实际费用合并] as a"
Set DBOpen = DB.OpenRecordset(Mychecksql)
datasheet.Range("a3").CopyFromRecordset DBOpen'从a3开始写入
datasheet.Cells(1, 3) = Date
datasheet.Cells(1, 4) = Time
' MsgBox "实际下载完成"
End Sub
|
|