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] |
|