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