在 Excel 中建立 MySQL 資料庫連線
當一個 Excel 檔案裡儲存了非常多但是又不常用到的資料表單時,不訪可以利用資料庫與 Excel 連線,將資料儲存在 MySQL 資料庫裡,到要使用時再連線將所需的資料取出,不僅讓 Excel 操作更快速,也能擁有方便管理的資料庫系統!
這次是利用 Excel 自己寫了一個能夠紀錄比賽中每一球相關資料的自訂表單,讓我們能夠快速的紀錄賽程獲得大量的相關資訊,然後再用 VBA 寫出與 MySQL 連線的幾個功能按鈕,把資料通通儲存在資料庫管理並讓 Excel 可即時取用。
這次是利用 XAMPP 做一個快速簡易的資料庫,首先安裝好 XAMPP 後並開啟,將 Apache 與 MySQL 點選 Start 開啟連線。(也需要安裝 ODBC Driver for MySQL)
接著開啟瀏覽器輸入 localhost 調整設定,管理密碼設為 123456,進入資料庫的頁面如下;並手動建立一個新的資料庫,稍後讓 Excel 連線使用。
進入 gamedata 資料庫內新增一個 MaLong_MattiasFalck 資料表,並將要輸入的每種資料格式設定好。
下圖可以看到建立好的資料庫以及資料表結構。
點擊資料表進入,會告訴你資料表裡面目前是空的。
當資料庫及資料表設定完成後,回到 Excel 開啟開發人員並點擊 Visual Basic,新增一個模組將下面兩組程序寫入;其中匯入資料程序接收 A、B、C、D、E、F、G 和 H 參數,分別是儲存格第二行的一到八欄,還有其他重點:建立資料庫 ADO 連線物件、接著開啟資料庫(相關語法可查詢下圖的連線語法網站)、編寫 SQL 字串和執行 SQL 字串。
連線資料庫語法網站,要更改的有資料庫名稱以及密碼。
接著再新增一個刪除資料庫中資料表內容的程序,也是先建立連線物件、開啟資料庫連線、SQL 操作語法和最後的執行語法;主要程式都是這四個步驟,然後更改要執行的 SQL 指令而已。
剛剛已經寫好新增一筆資料並刪除的測試程序了,再來就是將 Excel 中所有的資料上傳至資料庫的程序:用簡單的迴圈來執行每一行資料的上傳動作,並啟用 StatusBar 來觀看進度狀況。
當資料全部上傳至資料庫之後,要取用時就可以隨時從資料庫匯至新工作表這個程序來使用:也是先建立連線物件、開啟連線、語法描述並執行語法。當語法執行完畢後,資料會暫存在 rs 裡(Recordset),可以使用 CopyFromRecordset rs 來把資料傳送至新工作表中以 A1 起始的儲存格上。
最後只要在要上傳的資料表中插入表單控制項的按鈕,並指定剛剛寫好的程序(巨集)完成設定。
將剛剛寫好的四個程序都指定給四個按鈕之後,右鍵更改適當的按鈕名稱。
開始執行第一個按鈕測試。
接著到瀏覽器來看資料庫資料表中是否有出現一筆新的資料。
執行第二個按鈕,將剛剛匯入的一筆測試資料刪除。
再回到瀏覽器的資料表查看,確認資料已經刪除。
測試第三個按鈕,匯入所有此資料表中的所有資料。
Excel 表單左下角的狀態欄。
再到資料庫查看,發現所有資料皆已經完成上傳!
點選最後一個按鈕,將資料抓回來新的工作表上也成功了!
根據不同資料存取的方式,只要修改 SQL 字串語法就能完成,而這只有紀錄最重要簡單的幾項做法。
這次實作完成後發現一件事情,設計比賽紀錄系統的自訂表單,遠比在寫資料庫連線 Excel 的時間多出了很多很多…不過在紀錄比賽的細節時也真的非常快速且直覺,真感謝自己的龜毛來設計出對於自己來說非常好用的表單!可以開始大量紀錄比賽來做後續大數據分析實作了~
(因為表單在設計功能的時候有考慮一些:換發規則、球路的點擊方式、球路的紀錄可視框還有自動計算板數等等。這些都讓紀錄比賽的時候只要專注:是"誰"用了什麼樣的"方式"來得分,就可以輕鬆完成賽程紀錄。)