前景提要
要進行工作簿資料的合併,常規操作第一步都是去開啟工作簿,這個一點都沒有問題
在之前學習工作簿的合併的時候,我們也是經常這樣操作,慣用的程式碼就是workbook。open
但是開啟工作簿也有一個弊端,那就是會花費時間去等待工作簿的開啟
比方說上面這個圖片,你可能要花點時間去等待他的啟動,這就會浪費我們不少的時間
如果能夠跳過這個過程,那執行整個程式的速度就會快上很多
那麼如何跳過呢?
這就是我們今天的重點了
場景說明
這是我們今天的工作場景,我們需要將當前資料夾內所有的工作簿中,姓名為A1的人的成績全部彙總到一個工作簿中
當然還是在不開啟工作簿的前提下來完成的,那麼如何實現這樣的效果呢
這裡我們會接觸到一個新知識點,就是SQL
程式碼區
Sub tssss()
Dim sth As Worksheet, sql As String
Set sth = ActiveSheet
Set conn = CreateObject(“adodb。connection”) ‘建立ado物件
Set Rst = CreateObject(“ADODB。recordset”) ’建立記錄集
pathn = ThisWorkbook。Path
f = Dir(pathn & “\”)
Do While f <> “”
l = Cells(Rows。Count, 1)。End(xlUp)。Row
If f <> “5-22。xlsm” Then
k = k + 1
sql = “Select * from [Sheet1$] Where 姓名= ‘A1’”
With ActiveSheet
conn。Open “Provider=Microsoft。ACE。OLEDB。12。0;extended properties=excel 12。0;data source=” & ThisWorkbook。Path & “/” & f
Set Rst = conn。Execute(sql)
If k = 1 Then
For i = 0 To Rst。Fields。Count - 1
。Cells(1, i + 1) = Rst。Fields(i)。Name
Next i
。Cells(2, 1)。CopyFromRecordset conn。Execute(sql)
Else
。Cells(l + 1, 1)。CopyFromRecordset conn。Execute(sql)
End If
End With
conn。Close
End If
f = Dir()
Loop
End Sub
看看效果
可以看到整個過程很快的,螢幕都沒有閃動,就已經完成了資料的彙總了。
相較於之前我們看到的透過workbook。open方法來彙總資料,效率提升了很多。
程式碼解析
那我們來學習下今天的程式碼
前面我們說了,今天會引入一個新的知識點,就是SQL
相信對於很多小夥伴來說,SQL並不算是太陌生的東西,畢竟很多程式語言都會使用到SQL
讓大家感覺到吃驚的,應該就是在VBA中居然可以使用SQL
其實Excel 也是支援SQL的,這點從Excel能夠匯入資料庫檔案和關聯資料庫檔案就能夠知道,那麼進入正題,VBA中如何使用SQL呢?
Set conn = CreateObject(“adodb。connection”) ‘建立ado物件Set Rst = CreateObject(“ADODB。recordset”) ’建立記錄集
老規矩,你既然在VBA中使用了不屬於VBA的東西,那麼你肯定要申明一下了,如何申明,就是上面這兩個程式碼
那麼SQL最重要的核心是什麼呢?SQL語句
sql = “Select * from [Sheet1$] Where 姓名= ‘A1’”
就是他
和普通的SQL語句並沒有太大的差異。
但是這裡有兩點需要注意
第一點
注意,這裡一定要有[],[]裡面的名稱要和我們要彙總的資料來源的工作表的名稱保持一致,這一點一定要注意
還有就是工作表的名稱後面一定要加上一個$,這一點也不能少,不要SQL語句就沒有辦法執行了。
第二點
留意A1前後要有引號,代表是一個文字,這一點也不能忽略
有了SQL語句之後,我們還需要告訴SQL一個事情,去哪裡執行?執行開啟什麼樣的檔案
conn。Open “Provider=Microsoft。ACE。OLEDB。12。0;extended properties=excel 12。0;data source=” & ThisWorkbook。Path & “/” & f
Set Rst = conn。Execute(sql)
就是他們兩個。雖然看起來比較長,但是也是模組化的東西,直接套用就好
有了這些資訊之後就可以執行SQL語句了。
剩下的都是套路的操作啦。
最終將結果寫入對應的單元格即可
。Cells(l + 1, 1)。CopyFromRecordset conn。Execute(sql)
寫入的程式碼就是他