Sub Export2Mysql()
'將Excel當中的資料轉入資料庫中
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
Set conn = New ADODB.Connection
'這裡要換成你的伺服器 庫名 用戶名 密碼
conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=xxx.xxx.xxx.xxx;" & " DATABASE=test;" & "UID=test WD=password; OPTION=3"
conn.Open
'準備創建表
conn.Execute "drop table if exists test"
'注意這裡的各列類型設定
conn.Execute "create table test(name text,pass text)"
'按行導入,這裡假設第一列存的是name，第二列存的是pass
For i = 1 To 20
conn.Execute "insert into test(name,pass) values('" & Cells(i, 1).Text & "','" & Cells(i, 2) & "')"
Next i
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
'使用下面的代碼驗證
rs.Open "select * from test", conn
rs.MoveFirst
Do Until rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Value,
Next
rs.MoveNext
Debug.Print
Loop
rs.Close
conn.Close
End Sub作者: kimbal 時間: 2011-10-23 13:21
