Mas Omez,

saya sharing procedure atau macro yang pernah saya buat untuk memanggil data 
dari database mysql dengan menggunakan query:

 

‘program dibuat dengan  menge-set object reference Microsoft activex data 
object (microsoft ADO)

‘0. Procedure connect ke mysql

'********************************************

Function connectMysql(username As String, passwd As String, serverIP As String, 
db As String, conn As ADODB.Connection) As Boolean

'source :http://www.freevbcode.com/ShowCode.asp?ID=6187

   conn.CursorLocation = adUseClient

   conn.ConnectionString = "DRIVER={MySQL ODBC 5.2 Unicode 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 Then

     Err.Clear

    connectMysql = False

    Else

    connectMysql = True

    End If

End Function

‘1. Prosedure membuat object recordset berdasarkan connection dan input SQL 

Sub OpenRecordSets(conn As ADODB.Connection, ByVal sSQL As String, Rs As 
ADODB.Recordset)

    Dim cRs As New ADODB.Recordset

    cRs.Open sSQL, conn

    Set Rs = cRs

End Sub

 

‘2. Prosedur untuk merubah data recordset menjadi variant berdimensi

‘saya lebih suka menggunakan variant berdimensi daripada menulis langsung di 
cell excel karena prosesnya lebih cepat.

 

Function RecsToVariant(Rs As ADODB.Recordset, Optional WithHeader As Boolean) 
As Variant()

    Dim varRS() As Variant, i As Integer, j As Integer, intCol As Integer

    

        intCol = Rs.Fields.Count

        If Not WithHeader Then

            i = -1

            ReDim Preserve varRS(Rs.RecordCount - 1, intCol - 1)

        Else

            ReDim Preserve varRS(Rs.RecordCount, intCol - 1)

            i = 0

            For j = 0 To intCol - 1

                varRS(0, j) = Rs.Fields(j).Name

            Next j

        End If

        Rs.MoveFirst

        While Not Rs.EOF

            i = i + 1

            For j = 0 To intCol - 1

                varRS(i, j) = Rs(j).Value

            Next j

            Rs.MoveNext

        Wend

        Rs.Close

    RecsToVariant = varRS

End Function

 

‘3. procedure untuk menampilkan variant berdimensi ke sheet

 

Sub ViewRecordsOnSheet(rg As Range, vRecs As Variant)

    With rg.Resize(UBound(vRecs, 1) + 1, UBound(vRecs, 2) + 1)

        .Value = vRecs

        .EntireColumn.AutoFit

    End With

End Sub

 

‘4. Cara penggunaan

Sub TamplikanQuery()

dim conn As New ADODB.Connection

    If Not connectMysql(sUser, sPwd, sServer, sDb, conn) Then

        MsgBox "Gagal buka database", vbCritical

        exit sub

    End if

dim Rs As New ADODB.Recordset

dim hasilQuery as variant

sql=”SELECT acc.uraian FROM `acc`WHERE ACC.ID>1;”

 

OpenRecordSets conn,sql,Rs

hasilQuery= RecsToVariant(Rs, TRUE)’isikan TRUE jika akan menampilkan nama 
fieldnya

ViewRecordsOnSheet Range(“A1”),hasilQuery

End Sub

 

‘’selamat mencoba. mohon maaf jika ada yang masih salah.

 

wassalam

-zainul

 

From: [email protected] [mailto:[email protected]] 
Sent: Tuesday, March 24, 2015 4:18 PM
To: [email protected]
Subject: [belajar-excel] Memangil Data Di Localhost Mysql

 

  

Slemat Sore Master,,,,Saya ingin bertanya bisakah saya memangil data di
localhost mysql dan ditampilkan di sheeet excel..dengan macro,,saya
mengunakan conecctor ODBC dan sudah saya koneksikan dengan databese yg ada
di localhost dan sukses..bagaimanakah koding,a untuk memangil data tersebut
ke excle... terima kasih semoga para master bisa memberikan pencerahan
atas masalah ini.. berikut saya lampirkan
tampilanya.. koneksi222.jpg
<http://milis-belajar-excel.1048464.n5.nabble.com/file/n5729702/koneksi222.jpg> 
koneksi.jpg
<http://milis-belajar-excel.1048464.n5.nabble.com/file/n5729702/koneksi.jpg> 
MYsql.xlsx
<http://milis-belajar-excel.1048464.n5.nabble.com/file/n5729702/MYsql.xlsx> 

--
View this message in context: 
http://milis-belajar-excel.1048464.n5.nabble.com/Memangil-Data-Di-Localhost-Mysql-tp5729702.html
Sent from the Milis Belajar Excel mailing list archive at Nabble.com.



Kirim email ke