Re: Connecting to MySQL from Excel 2007

[Jay Alverson]

It's very easy if you use Excel Macros or VBA... if you don't then sorry...

This uses Excel's QueryTables feature.

Sub FileDSN()
    With ActiveSheet.QueryTables.Add(Connection:="ODBC;FileDSN=C:\Writing\Winbatch\MYSQL\VBS-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:\Writing\Winbatch\MYSQL\VBS-MySQL.dsn"
        .Refresh BackgroundQuery:=False
    End With
End Sub

The FileDSN is just a text file you can create in Notepad or via a script and doesn't require any user interaction. It must have the .dsn extension

The contents looks like this...

[ODBC]
DRIVER=MySQL ODBC 5.1 Driver
database=gurps
uid=Winbatch
pwd=WBTPassword
server=localhost
port=3306

and that's it.

All you have to do is change the path/name of the .dsn to yours and change
all the database particulars in the .dsn file to fit your setup.

Took me about 1 minute.

You can use Excel to record a macro of adding a QueryTable via the DATA menu
then hitting IMPORT EXTERNAL DATA --> NEW DATABASE QUERY. The result is the
above macro.

>

Thanks, Jay