Coba bantu pakai cara saya yang sedang belajar MySQL. 1. Download dulu "MySQL ODBC 5.1 Driver Connector" di http://dev.mysql.com/downloads/connector/odbc/5.1.html
2. Install program di atas. 3. Buat Macro idenya didapat dari http://www.freevbcode.com/ShowCode.asp?ID=6187 sudah saya aplikasikan di program timesheet User Interfacenya pakai Excel, databasenya MySQL. Rencana mau saya migrasi ke php. Dengan modifikasi sbb: 3.1. Set reference library ke Microsoft ActiveX Data Objects 3.2. Tulis atau copy kode yang ada di attachment Silahkan mencoba. ________________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of chusk soepono Sent: Thursday, January 07, 2010 8:23 AM To: [email protected] Subject: ]] XL-mania [[ Link excel ke MySQL Dear Excel Expert, Ada yang punya pengalaman me-link excel ke data base MySQL menggunakan ODBC /ADO?, Mohon dishare trick macro nya dong? Misalnya diinginkan membuat sheet di excel ngelink dengan: database : DBmySQL Tabel : tblmySQL Field :FieldmySQL1,FieldmySQL2,FieldmySQL3 Ingin ditulis ke worksheet : Sheet :sheet1 Kolom : KolomA,KolomB,KolomC catatan: Jumlah record sifatnya dinamis -->bila ditambah/didelete di MySQL bisa langsung update di Excelnya. MySQL servernya ada dikomputer lain dengan IP : 192.168.1.5 IP Excel nya : 192.168.1.6 user mySQL : usersql Password : passwordmysql Mohon dibantu step-stepnya ya ... Terima kasih, Adi
<<image001.jpg>>
Option Explicit
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db As String
dim g_username as string,g_passwd as string, g_serverIP as string
'********************************************
Private Function connectMysql(username As String, passwd As String, serverIP As
String, db As String, conn As ADODB.Connection, rs As ADODB.Recordset)
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & serverIP
& ";UID=" & username & ";PWD=" & passwd & ";DATABASE=" & db & ";" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841
conn.Open
End Function
Function CanConnectMySQL(username As String, passwd As String, serverIP As
String, db As String) As Boolean
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & serverIP
& ";UID=" & username & ";PWD=" & passwd & ";DATABASE=" & db & ";" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841
On Error Resume Next
conn.Open
If Err.Number = 0 Then
CanConnectMySQL = True
Else
CanConnectMySQL = False
End If
End Function
Private Function CanOpenRecordset(byVal ssql As String) As Boolean
On Error Resume Next
Call connectMysql(g_username, g_passwd, g_serverIP, g_db, conn, rs)
If Err Then
MsgBox "cannot connect database..."
CanOpenRecordset = False
Exit Function
Else
CanOpenRecordset = True
End If
rs.Open ssql, conn
End Function
'********************************************
Sub AmbilRecordDariServerMySql()
g_username = usersql
g_passwd = passwordmysql
g_serverIP = 192.168.1.5
g_db="DBmySQL"
If CanConnectMySQL(g_username, g_passwd, g_serverIP, g_db) Then
if CanOpenRecordset("select FieldmySQL1,FieldmySQL2,FieldmySQL3 from
tblmySQL;") then
'buat macro di sini untuk menulis recordset ke excel.
end if
Else
MsgBox "Tidak bisa nyambung"
End If
End Sub
