This works on my pc. Obviously you'll have to change the following in the connection string...
SERVER= {wherever your MySQL server is located}
DATABASE= {whatever db you use}
UID=
PWD=
sqlstr = "SELECT * FROM testtable;" {change the table name}
' connect to the test db, with UID/PWD and select all records from testtable and place the contents of the recordset into Excel...
dim MySQLConn, sqlstr, MySQLRS
Set MySQLConn = CreateObject("ADODB.Connection")
MySQLConn.open = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=test;UID=Winbatch;PWD=WBTPassword;OPTION=35;"
sqlstr = "SELECT * FROM testtable;"
Set MySQLRS = CreateObject("ADODB.Recordset")
MySQLRS.open sqlstr, MySQLConn, 1,4,1
dim Excel
Set Excel = CreateObject("Excel.Application")
Excel.visible = True
Excel.Workbooks.Add
Excel.Workbooks(1).Worksheets(1).Range("A2").CopyFromRecordset MySQLRS
MySQLConn.close() ' close the connection
Set MySQLConn = Nothing
See the VBA example in the previous post to insert the Field Names as a header row.
You can also get ADO to create a .DSN file using an extra keyword in the connection string. This way you don't have to supply a connection string in subsequent connections, all the info can be in the DSN. Use the MS-SQL ref below and scroll down to "SaveFile" and the following code. You'd have to go in by hand and add the PWD=WBTPassword {change to yours} pair into the .DSN file.
' now open a connection using a dsn file without that big, long string...
MySQLConn.open = "FileDSN=J:\Temporary\WBT-MySQL.dsn"
some of the MS-SQL Server pairs work for MySQL...
http://msdn.microsoft.com/en-us/library/ms130822.aspx
and
http://dev.mysql.com/doc/refman/5.0/en/connector-net-examples-mysqlconnection.html
>
Thanks, Jay