How to connect ms excel-2007 with mysql

Question

Establishing a MySQL connection with excel 2007. On Windows 7, 64-bit, I'm using. using MySQL 5.1 32-bit with excel 2007 32-bit. How can I import the data from my MySQL table into an Excel file?

Answer 1

Try this:

Function runQuery()
Dim cn As Object
Dim rs As Object
Dim strSql As String
Dim strConnection As String

Set cn = CreateObject("ADODB.Connection")

'Set your DB particulars
strConnection = "Data Source=MySQLExcel;Driver={MySQL ODBC 5.5.25a Driver};Server=" & _
                "localhost" & ";Database=" & "your-db-name" & _
                ";Uid=" & "your-user-name" & ";Pwd=" & "your-password" & ";"

    cn.Open strConnection
'Set your MySql query, i used "Select" query
    strSql = "SELECT * from Table-Name;"

    Set rs = cn.Execute(strSql)

'In case of "Select query" set your range to show records
'In case of "insert/edit/delete query" exclude next two lines
    Worksheets("SearchResults").Range("a4:xfd1048576").ClearContents
    Range("b4").CopyFromRecordset rs

'close the connection
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing