mac excel mysql数据库,在Mac Excel 2011中从Mysql DB获取数据的VBA代码

I was using ADODB code in Windows to fetch data from Mysql Database and it was working fine. However, I can not seem to get my Excel workbook (using ADODB) to work with Excel Mac 2011. After a lot of googling, I found an ODBC connector from Actual tech and I was able to fetch three rows using Microsoft Query. But I want this to be done using VBA code but have been unable to do so. Has anyone gotten this to work? If yes, can you please provide me with a sample code. Thanks in Advance !!

P.S: I know there is a similar existing question but the link provided in the answer is no longer working. hence, I have asked a new question

Also, If anyone needs the link to do it through Microsoft Query, here is the link:

http://www.agentjim.com/MVP/Excel/2011Relational7Queries.html

Here is the code I use in Windows:

Sub getMysqlDBdata()

Dim Cn As Object

Dim sqlQa as string

dim temparray1 as variant

Source = "MySQL"

mysql_driver = "MySQL ODBC 5.2 ANSI Driver"

sqlQa = "select * from test.TestTable;"

Set Cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

Cn.Open "Driver={" & "MySQL ODBC 5.2 ANSI Driver" & "};Server=" & "127.0.01" & ";Database= test;UID=" & "root" & ";PWD=" & "12345"

rs.Open sqlQa, Cn, adOpenStatic

temparray1 = rs.GetRows()

rs.Close

Set rs = Nothing

End Sub

解决方案

After a lot of googling I came across this sample code provided by Bryan Duchesne in MSDN:

Sub TestSqlConnection()

Dim sqlstring As String

Dim connstring As String

Dim sLogin As String

sLogon = "Uid=myUserID;Pwd=myPassowrkd;"

sqlstring = "select * from zitemloc"

connstring = "ODBC;DSN=myDSN;" & sLogon

ActiveSheet.Range("B1:t2000").Clear

Dim qt As QueryTable

For Each qt In ActiveSheet.QueryTables

qt.Delete

Next qt

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("B1"), Sql:=sqlstring)

.BackgroundQuery = False

.Refresh

End With

Set qt = ActiveSheet.QueryTables(1)

Dim rowCount As Integer

rowCount = UBound(qt.ResultRange.Value)

Dim ix, iy As Integer

Dim data As Variant

Dim colCount As Integer

colCount = qt.ResultRange.Columns.Count

For ix = 1 To rowCount

If Not IsArray(data) Then

ReDim data(rowCount - 1, colCount - 1)

End If

For iy = 1 To qt.ResultRange.Columns.Count

data(ix - 1, iy - 1) = qt.ResultRange.Value2(ix, iy)

Next

Next

End Sub