在Mac Excel 2011中从Mysql DB获取数据的VBA代码 [英] VBA code to fetch data from Mysql DB in Mac Excel 2011

问题描述

我在Windows中使用ADODB代码从Mysql数据库中获取数据，并且工作正常。但是，我似乎无法使用Excel工作簿（使用ADODB）来处理Excel Mac 2011。经过大量的搜索，我发现了一个实际的技术的ODBC连接器，我可以使用Microsoft Query获取三行。但是我希望使用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：我知道有一个类似的现有问题，但答案中提供的链接不再工作。因此，我已经提出了一个新问题

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

另外，如果有人需要链接通过Microsoft Query执行，这里是链接：
http://www.agentjim.com/MVP/Excel/2011Relational7Queries.html

Also, If anyone needs the link to do it through Microsoft Query, here is the link: http://www.agentjim.com/MVP/Excel/2011Relational7Queries.html

这是我在Windows中使用的代码：

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

推荐答案

经过很多谷歌搜索，我遇到了由 Bryan Duchesne

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

这篇关于在Mac Excel 2011中从Mysql DB获取数据的VBA代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！