How to connect ms excel-2007 with mysql.

Question

How to connect excel-2007 with mysql. I am on windows 7, 64 bit. Using excel-2007 32 bit and mysql version 5.1 , 32 bit. Can Anyone help me how to import my mysql table data in excel file. Thanks in advance.

Answer 1

I am adding to @Anil Chahal answer:

After configuration as described by @Anil Chahal, following code can be used to fetch data from MySql into Excel.

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

End Function