Re: Export mySQL to Excel

[Jay Alverson]

Headers are simple just adjust for ADO's zero-based .field subscripts and Excel's need for 1-based column subscripts...



' login to the test db and select all records, then place headers + data 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
dim i
' .Cells(column, row) convention, by ADO recordset uses 0-based versus excel's
' 1-based subscripts...
for i = 0 to MySQLRS.fields.count-1
Excel.Workbooks(1).Worksheets(1).Cells(i+1, 1).value = MySQLRS.fields(i).name
next

Excel.Workbooks(1).Worksheets(1).Range("A2").CopyFromRecordset MySQLRS


MySQLConn.close() ' close the connection
Set MySQLConn = Nothing




>

Thanks, Jay