excel链接mysql

下载mysql odbc

下载地址

安装配置-百度经验
MySQL ODBC 5.3 Unicode Driver

excel vba

打开一个数据库连接去运行SQLStr里的语句, 等返回数据将之放到一个数组变量里面去，最后用一个循环将数据从数组放到excel的单元格里面去。参见知乎-吴棋仁回答

Sub connect()

    Dim Password As String
    Dim SQLStr As String
    'OMIT Dim Cn statement
    Dim Server_Name As String
    Dim User_ID As String
    Dim Database_Name As String
    'OMIT Dim rs statement
    '定义返回数据对象，获取数据库连接信息在对应的单元格里
    Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
    Server_Name = Range("b2").Value
    Database_Name = Range("b3").Value ' Name of database
    User_ID = Range("b4").Value 'id user or username
    Password = Range("b5").Value 'Password

   '定义SQL语句
    SQLStr = "SELECT * FROM keyword_csv"
    '打开一个mysql ODBC 连接
    Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
    Cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & _
            Server_Name & ";Database=" & Database_Name & _
            ";Uid=" & User_ID & ";Pwd=" & Password & ";"
    '打开的连接运行SQLStr
    rs.Open SQLStr, Cn, adOpenStatic

    Dim myArray()
    '将返回的数据放到myArray数组变量里面去
    myArray = rs.GetRows()

    '获取返回数据的列数和行数大小
    kolumner = UBound(myArray, 1)
    rader = UBound(myArray, 2)

    '循环每一列和行写入excel单元格。
    For K = 0 To kolumner ' Using For loop data are displayed
        Range("a5").Offset(0, K).Value = rs.Fields(K).Name
        For R = 0 To rader
           Range("A5").Offset(R + 1, K).Value = myArray(K, R)
        Next
    Next
End Sub