如何使用EXCEL連接MYSQL





















(1) 建立ODBC連線

取得ODBC驅動程式並安裝
https://dev.mysql.com/downloads/connector/odbc/

Install MySQL Connector ODBC




(2) 建立資料來源




(3) 啟用EXCEL的開發人員



(4) 設定巨集安全性


(5) 透過VBA使用ODBC連線MYSQL



範例一、從MYSQL抓出資料秀在EXCEL上
Sub ShowData()
Range("A1:C99").Clear
On Error GoTo ErrorHandler
Set myCon = CreateObject("ADODB.Connection")
myCon.Open "Driver={MySQL ODBC 5.3 UNICODE Driver};Server=server-ip;Database=dbname;User=root;Password=yourpassword;Option=3;"
A = CStr(Cells(1, 1))
B = CStr(Cells(1, 2))
Sql = "select * from t"
Set myRs = myCon.Execute(Sql)
Range("A3").CopyFromRecordset myRs
Exit Sub
ErrorHandler:
MsgBox (Err.Description)
End Sub

範例二、插入EXCEL上的資料到MYSQL表單
Sub InsertData()
On Error GoTo ErrorHandler
Set myCon = CreateObject("ADODB.Connection")
myCon.Open "Driver={MySQL ODBC 5.3 UNICODE Driver};Server=server-ip;Database=dbname;User=root;Password=yourpassword;Option=3;"
A = CStr(Cells(1, 1))
B = CStr(Cells(1, 2))
Sql = "insert into t (id,f1) values (" + A + "," + B + ")"
Set myRs = myCon.Execute(Sql)
Exit Sub
ErrorHandler:
MsgBox (Err.Description)
End Sub

範例三、從MYSQL表單中刪除指定的資料
Sub DeleteData()
For Each OneCell In Selection
A = CStr(OneCell.Value)
Next
On Error GoTo ErrorHandler
Set myCon = CreateObject("ADODB.Connection")
myCon.Open "Driver={MySQL ODBC 5.3 UNICODE Driver};Server=server-ip;Database=dbname;User=root;Password=yourpassword;Option=3;"
Sql = "delete from t where id=" + A
'MsgBox (Sql)
Set myRs = myCon.Execute(Sql)
Exit Sub
ErrorHandler:
MsgBox (Err.Description)
End Sub

範例四、更新MYSQL表單中的資料
Sub UpdateData()
On Error GoTo ErrorHandler
A = CStr(Cells(1, 1))
B = CStr(Cells(1, 2))
For Each OneCell In Selection
C = CStr(OneCell.Value)
Next
Set myCon = CreateObject("ADODB.Connection")
myCon.Open "Driver={MySQL ODBC 5.3 UNICODE Driver};Server=server-ip;Database=dbname;User=root;Password=yourpassword;Option=3;"
Sql = "update t set id=" + A + ", f1=" + B + " where id=" + C
Set myRs = myCon.Execute(Sql)
Exit Sub
ErrorHandler:
MsgBox (Err.Description)
End Sub

留言

這個網誌中的熱門文章

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理