RE: ]] XL-mania [[ Link excel ke MySQL

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

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: XL-mania@yahoogroups.com [mailto:XL-mania@yahoogroups.com] On Behalf Of chusk soepono

Sent: Thursday, January 07, 2010 8:23 AM

To: XL-mania@yahoogroups.com

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

 

 

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4749 (20100106) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com