Excel Power Query + VBA – 寫入 ODBC ( MySQL )

前面一篇利用 ODBC 讓 Excel 讀取資料庫的資料 – https://benjr.tw/104821 , 那可以把 Excel 資料回寫到資料庫嗎? 可以利用 VBA 的功能來做到.

參考文章

https://www.youtube.com/watch?v=VRm8tUerrZU
https://terry28853669.pixnet.net/blog/post/64083631-excel-vba%E6%94%B9%E7%94%A8mysql%E7%95%B6%E6%88%90%E8%B3%87%E6%96%99%E5%BA%AB%E5%AD%98%E6%94%BE%E5%A4%A7%E9%87%8F%E8%B3%87%E6%96%99

測試環境 Windows 2019 Stnadrad + Excel 2016 與 MySQL Community 8.0.30 + Connector/ODBC 8.0.30 (版本都是 64 bits)

架構如下 (MySQL 與 Excel 皆在同一台機器上)

MySQL Community

下載並安裝 MySQL Community 8.0.30 – https://dev.mysql.com/downloads/mysql/ , 需安裝 MySQL Server 與 Connector/ODBC . 詳細安裝請參考 https://benjr.tw/94826

接下來建立測試用的資料.

建立 Database(Schema) 與 table

CREATE SCHEMA excel;

CREATE TABLE excel.staff (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) NULL,
  age INT NULL,
  PRIMARY KEY (id));

新增資料 (Records)

INSERT INTO excel.staff (name, age) VALUES ('ben', '30');
INSERT INTO excel.staff (name, age) VALUES ('lily', '8');

SELECT * FROM excel.staff;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | ben  |   30 |
|  2 | lily |    8 |
+----+------+------+

Create MySQL User & Assign privilege
透過 MySQL WorkBench 建立 ben@% 使用者 (% 代表全域,可以從任何 IP 登入) ,權限請自行決定 (測試時可先開成 DBA ,權限全開)

Excel

Excel 端需安裝以下工具.

Visual Studio 2019 x64 redistributable – https://learn.microsoft.com/en-US/cpp/windows/latest-supported-vc-redist?view=msvc-170#visual-studio-2015-2017-2019-and-2022
MySQL Connector/ODBC
如果 Excel 不是跟 MySQL Server 在同台機器上時需要額外安裝 MySQL Connector/ODBC – https://dev.mysql.com/downloads/connector/odbc/ 下載與安裝後開啟 odbc data source (64bits) 並測試一下連線.

從開始鍵入 odbc 來搜尋.

Add 新增連線來測試一下 ( 資料會儲存為 test.dsn )

填入 IP , Port (3306) , 使用者 (root 預設只能在本地端,可以用剛剛建立的 ben 使用者), 密碼與前面建立好的 Database(Schema) excel , 並 Test 是否出現 Connection Successful

回到 Excel 下載資料庫資料

資料 / 新查詢 / 從其他來源 / 空白查詢

在查詢鍵入 Power Query 指令,關於 Odbc.Query 請參考 – https://learn.microsoft.com/zh-tw/powerquery-m/odbc-query

= Odbc.Query("DRIVER=MySQL ODBC 8.0 Unicode Driver;USER=ben;PORT=3306;SERVER=192.168.31.129;DATABASE=excel", "SELECT * FROM excel.staff;")

請指定連接方式 /編輯認證 , 這邊需鍵入資料庫使用者與密碼 (預設的 root 只能用在本地端 127.0.0.1 才能連線)
資料擷取正確就可以看到資料庫的資料了.
建立到新的 worksheet.

從 Excel 上傳資料到資料庫

開啟 VBA
預設會看不到 VBA 的選單,需到檔案 / 選項 / 自訂功能區勾選開發人員
開啟開發人員/ 巨集或是 Visual Basic (空白區按右鍵 / 插入 / 模組)

模組 / Module1 建立 VBA 內容
```
Sub AddAll()
   For i = 2 To Range("A1").End(xlDown).Row
       A = Cells(i, 2)
       B = Cells(i, 3)
       Call ADOCon(A, B)
   Next
   MsgBox "Upload Completed!!", vbInformation
End Sub
```
- Range(“A1”).End(xlDown).Row
  抓資料從 A1 開始一直往下一行直到無資料為止.
  其中 xlToLeft (向左) , xlToRight (向右) , xlUp (向上) , xlDown (向下)
  關於 Range 請參考 – https://learn.microsoft.com/zh-tw/office/vba/api/excel.range(object)
- Cells(i, 2)
  把 Cells 指定的行與列的儲存格內容指到變數.
```
Sub ADOCon(A, B)
   Set myCon = CreateObject("ADODB.Connection")
   myCon.Open "DRIVER=MySQL ODBC 8.0 Unicode Driver;USER=ben;Password=111111;PORT=3306;SERVER=192.168.31.129;DATABASE=excel;"
   Sql = "INSERT INTO excel.staff(name, age) VALUES ('" & A & "' ," & B & " );"
   Set myRs = myCon.Execute(Sql)
End Sub
```
1. DRIVER=MySQL ODBC 8.0 Unicode Driver
  其他資料庫的連線 DRIVER 參數可以到 – https://www.connectionstrings.com/ 來查詢.
2. USER=ben;Password=111111
  需可以存取指定資料表的使用者(需全域,可以從任何 IP 登入)與密碼.
3. PORT=3306
  預設 MySQL TCP Port.
4. SERVER=192.168.31.129
  MySQL 的 IP.
5. DATABASE=excel
  剛剛建立的 excel Database(Schema)
6. INSERT INTO excel.staff(name, age) VALUES (‘” & A & “‘ , ” & B & “);
  上面 SQL 語法是將 Excel 資料寫入到 excel.staff 資料表,這需要懂 SQL 語法 , 請參考基礎 INSERT , SELECT , UPDATE , DELETE SQL 語法 – https://benjr.tw/95609
新增表單控制項開發人員/插入/按鈕

選擇 AddAll
新增資料並點選上傳鍵
檢視資料庫
資料是上傳了,但舊資料也上傳了一遍,這個需要去調整 SQL 語法去避免這個問題,這邊就不多談.