Anil Chahal Anil Chahal - 1 year ago 127
MySQL Question

How to connect ms excel-2007 with mysql.

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 Source

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
    Range("b4").CopyFromRecordset rs

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

End Function

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download