Connect to MySQL from Excel

from the Artful MySQL Tips List


Using Excel QueryTables:


Sub FileDSN()

  With ActiveSheet.QueryTables.Add( _

    Connection := "ODBC;FileDSN=cv:projectsexcel-mysql.dsn;", _

    Destination := Range("A1") _

  )

    .CommandText = "SELECT * from orders"

    .Name = "MySQL Test 1"

    .FieldNames = True

    .RowNumbers = False

    .FillAdjacentFormulas = False

    .PreserveFormatting = True

    .RefreshOnFileOpen = False

    .BackgroundQuery = True

    .RefreshStyle = xlInsertDeleteCells

    .SavePassword = False

    .SaveData = True

    .AdjustColumnWidth = True

    .RefreshPeriod = 0

    .PreserveColumnInfo = True

    .SourceConnectionFile = "C:WritingWinbatchMYSQLVBS-MySQL.dsn"

    .Refresh BackgroundQuery:=False

  End With

End Sub



FileDSN is a text file with a .dsn extension containing ...


[ODBC]

DRIVER=MySQL ODBC 5.1 Driver

database=DBNAME

uid=USR

pwd=PWD

server=localhost

port=3306



substituting appropriate values for USR, UID and DBNAME.

In Excel, record a macro adding a QueryTable via the DATA menu, then select Import External Data | New Database Query, and you get the above macro.

[from a post by Jay Alverson on the MySQL Forum]

Return to the Artful MySQL Tips page