標題:
ExcelVBA抓Access資料速度優化請益
[打印本頁]
作者:
a5345534
時間:
2021-10-13 18:17
標題:
ExcelVBA抓Access資料速度優化請益
Access,8,500筆資料
EXCEL,116,000筆資料
原諒我沒有權限上傳與分享網址....
邏輯是先到EXCEL抓取料號資料後,到Access資料庫抓取同料號的數量資訊,代入Excel
目標是透過Access資料庫搭配Excel VBA的模式讓臃腫的Excel函數能夠快一點,否則原本的資料太大,公司電腦無法負荷
但寫完程式後,卻發現抓的比我Excel用Vlookup還慢,只比對前1500筆就要15秒
是否我VBA哪裡寫的效率太低呢,麻煩高手助我指點迷津!!
Sub CreateQueryRS()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Dim cnADO As Object
Dim rsADO As Object
Dim strPath As String
Dim strSQL As String
Dim j As String
Dim i As Long
Set cnADO = CreateObject("ADODB.Connection")
Set rsADO = CreateObject("ADODB.RecordSet")
strPath = ThisWorkbook.Path & "\Database11.accdb"
Range("表格1[上期期末金額]").ClearContents
'ThisWorkbook.Sheets("11009進銷存明細表").Cells(2, 3).Value = ThisWorkbook.Sheets("11009進銷存明細表").Range("表格1[料品編號]").Rows.Count
On Error GoTo ErrMsg
cnADO.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath
For i = 0 To 1500
' For i = 0 To ThisWorkbook.Sheets("11009進銷存明細表").Range("表格1[料品編號]").Rows.Count - 1
j = ActiveSheet.Cells(4 + i, 3).Value
strSQL = "SELECT SUM(交易數量) FROM B1進貨資料 WHERE 料品編號= '" & j & "' AND 類別='進貨'"
rsADO.Open strSQL, cnADO, 1, 3
'Range("A410").CopyFromRecordset rsADO
ActiveSheet.Cells(4 + i, 1).CopyFromRecordset rsADO
rsADO.Close
Next i
cnADO.Close
Set rsADO = Nothing
Set cnADO = Nothing
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Exit Sub
ErrMsg:
MsgBox Err.Description, , "錯誤報告"
End Sub
複製代碼
作者:
samwang
時間:
2021-10-14 08:46
回復
1#
a5345534
原諒我沒有權限上傳
>> 上傳應該都可以上傳檔案
要改善速度,建議用陣列+字典
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)