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]

Last updated 29 Sep 2024

Return to the Artful MySQL Tips page