How to:Excel VBA 使用SQL方法快速拿取數據 (設定篇)

以下將會簡單介紹一下為何要用SQL來拿取EXCEL數據,和如何做

用SQL而不用EXCEL上方法的條件

那些時候需要用SQL (ODBC/ADO) 方法?

S1. 利用SQL方法,可以簡單地選擇數據, 如用WHERE,GROUP BY,ORDER BY...等SQL的DML方法.節省開發時間
S2. 如要通過多頁連接數據, 用SQL的JOIN比寫VBA更為直接

S3. 進行較大型工作,如插入多行數據,所需運行時間比用上VBA為少

(對SQL沒太大認識的朋友:簡單來說,SQL在這裡的用處,是可以在同一時間,
對某多個表來做統計,或用欄位作條件,抽出所需要的列.)

那些時候要用EXCEL VBA?

E1. 原數據不是完全的表(TABLE)型式
E2. 需要對數據表進行格式設定(如轉顏色或conditional formating等)
E3. 程序和數據表,在同一EXCEL檔下.

當然,兩個方法可以渾合使用,如E3,用SQL方法的話,可以先把數據分到另一個檔(如條件許可)

VBA SQL拿取數據範例
以下將會一步一步介紹如何抽取數據

下載範例檔案

下載範例數據檔案

範例一:把數據用表列些抽出

1.開啟新Excel活頁簿

2.啟動巨集/宏編輯 (參考如何編輯巨集)

3.Data Access Library

3A)加入ADO兩個library.
如果library不存在,可在這裡下載MDAC新版 (目前是2.8版本)

3B)OFFICE 2007 Data Connectivity Components

如果沒有安裝Office2007, 建議下載2007 Office System Driver: Data Connectivity Components
這個驅動可連接EXCEL2007,EXCEL2003或之前版本.

3C)在VBA編輯環境, 進入"工具"->"參考"
打勾ADO兩個LIBRARY

4.跟隨第二步的方法,加上以下代碼然後運行

sub subXlsSqlQuery()

'定義部份
Dim cn As ADODB.Connection 'Excel連接
Dim rs As ADODB.Recordset '得來的結果,會放到立這個rs的地方.
Dim strDataSrcXlsPath As String '數據庫EXCEL位置
Dim strQuery As String '用作拿數據的SQL
Dim strStartlocation as String '定義得來數據,放到那一個位置.


'I) 以下建立數據庫連接
strDataSrcXlsPath = "D:\Connection_data_many2.xlsm" '定立數據庫EXCEL位置

Set cn = New ADODB.Connection 
With cn
'確定連接所用的Library
.Provider = "Microsoft.ACE.OLEDB.12.0" 

'把連接指向指定Excel
'xtended properties為excel的版本, 通常為12 或 8, 
'12為2007Excel檔(如XLSX,XLSM). 8可相容1997-2003等excel(XLS)
.ConnectionString = "Data Source=" & strDataSrcXlsPath & _
";Extended Properties=Excel 12.0;"

'開始連接,這個時候ACE會打開excel
.Open

End With

'II) 以下運行SQL和回傳數據
'拿出一行資料,所用SQL架構大約為
'SELECT ... FROM ... WHERE
'
'SELECT 上的 * 用作指明拿出所有表上的欄位
'TOP 50 是指抽出頭50列數據
'
'FROM 從DATASHEET工作表抽出
strQuery = "SELECT top 50 * FROM [DataSheet$]" 
Set rs = cn.Execute(strQuery) '執行SQL

'III) 把數據抄至所需的位置
strStartlocation ="A1"

'III-1 把欄位名稱一個一地拿出
'rs.Fields.Count : 欄位數數
'rs.Fields(n).Name : 欄名
Dim lngColCounter as long
For lngColCounter = 0 To rs.Fields.Count - 1 
range(strStartlocation).offset(0,lngColCounter) = rs.Fields(lngColCounter).Name 
Next lngColCounter
'III-2 把數據庫資料列表拿出
ActiveSheet.Range(strStartlocation).offset(1,0).CopyFromRecordset rs

'IV) 關掉/清理連接
cn.Close
Set cn = Nothing
Set rs = Nothing

end sub

5.按執行(F5),效果將如下圖

範例二:抽出一行數據及自動填表

首先設定好範例一上1/2/3三個環境

4.在Excel上加入一新工作表,名為 Query
依如下格式和位置,配置工作表
D3為用戶輸入交易代號條件

5.加入以下新模組和代碼
由於大部份代碼跟範例的類似, 所以只介紹不同的部份

Sub subRetrieveByField()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
strDataSrcXlsPath = "D:\Connection_data_many2.xlsm"

Dim strDataSrcXlsPath As String

'I) 以下建立數據庫連接
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strDataSrcXlsPath & _
";Extended Properties=Excel 12.0;"
.Open

cn.CursorLocation = adUseClient

End With

'II) 以下運行SQL

'II-1) 準備SQL
Dim strQuery As String
Dim strQueryWorksheet As String
strQueryWorksheet = "Query" '數據查詢頁面位置

'拿出一行資料,所用SQL架構大約為
'SELECT ... FROM ... WHERE
'
'SELECT 用作指明拿出的欄位
'留意 (Order_Qty * Product_Price) as TotalAmt 是直接用SQL算出的欄位.
'TOP 1 為指定只拿一行數據
'也留意一下欄位大小寫,要跟EXCEL數據庫上的相同.
'
'FROM 從DATASHEET工作表抽出
'
'WHERE 抽出的行數條件,這裡用了 交易代號 Transaction_ID
strQuery = "SELECT top 1 " & _
" Transaction_Date, " & _
" Order_Qty, " & _
" Product_Name, " & _
" Product_Number, " & _
" Product_Color, " & _
" Product_Size, " & _
" (Order_Qty * Product_Price) as TotalAmt, " & _
" Product_Price " & _
" FROM [DataSheet$] " & _
" WHERE Transaction_ID = " & _
ThisWorkbook.Worksheets(strQueryWorksheet).Range("D3")

'II-2)執行SQL
Set rs = cn.Execute(strQuery)

'III) 把數據抄至所需的位置
Dim strStartlocation As String
Dim lngRowCounter As Long

If rs.RecordCount <= 0 Then 'rs.RecordCount 為有多少列數據,沒的話就顯示MSGBOX提示
MsgBox ("Transaction not found")
Else
With ThisWorkbook.Worksheets(strQueryWorksheet)
'把資料依名放入左方指明的格上
.Range("D6") = rs.Fields("Transaction_Date").Value
.Range("D7") = rs.Fields("Order_Qty").Value
.Range("D8") = rs.Fields("TotalAmt").Value

.Range("D11") = rs.Fields("Product_Number").Value
.Range("D12") = rs.Fields("Product_Name").Value
.Range("D13") = rs.Fields("Product_Color").Value
.Range("D14") = rs.Fields("Product_Size").Value
.Range("D15") = rs.Fields("Product_Price").Value
End With
End If

'IV) 關掉/清理連接
cn.Close
'rs.Close
Set cn = Nothing
Set rs = Nothing

End Sub

6.按運行後效果