如何使用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

VBA參考資料
https://terry55wu.blogspot.com/p/excelvba.html
https://terry55wu.blogspot.com/p/excel-vba.html