Re: Export mySQL to Excel

[Bruce Bock]

I tried your code and made the changes that I thought were needed and I'm getting a Compile error: Syntax error on the line containing MySQLConn.Close() any ideas on why it does not like this. Multiple examples on searching Google show this statement.



Public Sub Import()
' connect to the Asterisk db, with UID/PWD and select all records from table 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=192.168.6.2;DATABASE=asterisk;UID=root;PWD=password;OPTION=35;"
sqlstr = "SELECT * FROM asterisk;"
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(3).Range("A2").CopyFromRecordset MySQLRS


MySQLConn.Close()
Set MySQLConn = Nothing

End Sub