如何用VBA操作MySQL資料庫?如何直接使用Excel操作MySQL資料庫? ...
本文主要涉及:
- VBA中的MySQL環境配置
- VBA連接MySQL資料庫
- VBA讀寫MySQL數據
- 在Excel中連接MySQL資料庫及數據讀寫
系統環境:
- Windows 10
- Excel 2013
- XAMPP。其中集成的資料庫是MariaDB 10.1.13(如果不懂MariaDB為何物,可把它當成是MySQL。事實上它的使用和MySQL也幾乎完全一致)
1. VBA連接MySQL前的環境配置
系統中必須先安裝有MySQL服務,這個就不多說了。可以選擇使用官方安裝包,或者使用PHP集成環境中的資料庫都可以。
1.1 啟用腳本支持
在使用前,需要先在VBE中啟動資料庫連接支持。按下Alt+F11打開VBE,在菜單欄選擇“工具”-“引用”,在彈出的引用視窗中,找到"Microsoft ActiveX Data Objects 6.1 Library"和"Microsoft ActiveX Data Objects Recordset 6.0 Library",把前面的框勾選上,點擊確定即可。
1.2 安裝MySQL ODBC 連接服務
選擇操作系統及系統版本,下載對應的MSI文件安裝即可
1.3 添加ODBC數據源
打開“控制面板”-“管理工具”-“ODBC 數據源”,在彈出的視窗中的“用戶DSN”選項卡右側,選擇“添加”,在新資料庫源中會出現兩個MySQL驅動,分別為“MySQL ODBC 5.3 ANSI Driver”及"MySQL ODBC 5.3 Unicode Driver",很明顯兩者的區別在於編碼標準。我選擇的是Unicode版本。選中其中一個,點完成即可
1.1和1.2的配置順序可以隨意,但1.3必須在1.2之後,否則在ODBC數據源中添加新數據源時,是找不到MySQL選項的。
2. VBA連接MySQL
在按照1.1步驟配置了環境支持後,就可以在VBA中使用代碼連接MySQL了。
先定義連接對象
Dim conn as ADODB.Connection
Set conn = new ADODB.Connection
連接資料庫
conn.ConnectionString = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=localhost;DB=test;UID=root;PWD=root;OPTION=3;"
conn.Open
在這裡可以看到,Driver變數的值是必須要和數據源中添加的新數據源一致的,否則會提示找不到數據源。
致此,資料庫連接成功!
可以使用連接對象的State
屬性和Version
屬性查看資料庫狀態和版本(檢查是否連接成功)
MsgBox("資料庫狀態:" & conn.State & vbCrLf & "資料庫版本:" & conn.Version)
最後關閉資料庫連接
conn.Close
Set conn = Nothing
3. VBA讀寫MySQL數據表
3.1 讀取MySQL數據到Excel
代碼如下:
Sub db()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set rs = New Recordset
conn.ConnectionString = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=localhost;DB=test;UID=root;PWD=root;OPTION=3;"
conn.Open
Set rs = conn.Execute("select * from `test`", iRowscount, adCmdText)
Range("a1:b1").Value = Array("ID", "Name")
Range("A2").CopyFromRecordset rs
rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing
End Sub
相比前面的代碼,以上代碼多了Recordset
和conn.Execute
,Recordset
用於接收SQL查詢語句返回的結果集。conn.Execute
則用於執行SQL語句,其第一個參數為SQL語句,第二個參數為這個SQL語句影響到的行數,第三個參數是(我也不懂,照寫吧)。接下來的兩行Range
是用於把查詢結果複製到Excel表格中的。
3.2 寫入數據到MySQL
其實寫入數據,只需要把上例中的SQL語句改成UPDATE
或者INSERT
即可,就不多說了。
4. 在Excel中直接操作MySQL
MySQL推出了一個Excel插件,用於直接在Excel操作MySQL資料庫。
首先需要下載:Download MySQL for Excel
下載完運行安裝即可。
然後打開(重啟)Excel,打開時可能會詢問是否添加該插件。打開後,在菜單欄點開“數據”,即可在右側看到有個MySQL for Excel的東西。點開它,就會出現本地的MySQL資料庫。
可以右鍵單擊其中的資料庫,編輯連接信息:
或者雙擊打開資料庫目錄,依然打開數據表目錄,再選中其中一個數據表時,在下方會出現三個選項,分別是導入MySQL數據、編輯MySQL數據、添加MySQL數據。如果選中導入或編輯,都會在Excel中新增一個工作表用於操作數據,如果是選擇添加,則不會。至於具體的操作,就都是傻瓜式的了,這裡就不再多講了。