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.

