- 帖子
- 1
- 主題
- 1
- 精華
- 0
- 積分
- 2
- 點名
- 0
- 作業系統
- windows 10
- 軟體版本
- office 2007
- 閱讀權限
- 10
- 註冊時間
- 2017-1-11
- 最後登錄
- 2021-10-20
|
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
|
|
發表於 2021-10-13 18:17
| 
