Well I finally found a workaround to get excel vba for office 2004 to work with mysql. I hope I’m not repeating what’s already known.
I’ve installed MySQL Connector ODBC 3.pkg (from Microsoft?) and nothing else.
Here’s the workaround to send any command to a mysql database:
Sub mysql_command()
On Error GoTo 1000 ‘yes I know using goto’s is bad practice 😛
Sheets(“mysql_workspace”).Activate
‘In this case the user enters the query command into cells(1,5) and then clicks a command button which executes this macro.
sqlstring = Cells(1, 5).value
connstring = _ “ODBC;DSN=myodbc;UID=your_user_id;PWD=your_password;Database=your_schema”
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range(“A1”), Sql:=sqlstring)
.Refresh
End With
1000 end
‘——————You can stop here if you like or use the following code to return the result of your query into the excel sheet (you’ll have to remove “end” from line 1000).
‘My test table (called “test”) has two fields.
‘I’ve used these rows to dump the mysql tables so they are cleared everytime the macro runs a query.
Range(“A:A”).ClearContents
Range(“B:B”).ClearContents
sqlstring = “select * from test;”
connstring = _
“ODBC;DSN=myodbc;UID=your_user_id;PWD=your_password;Database=your_schema”
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range(“A1”), Sql:=sqlstring)
.Refresh
End With
End Sub
I hope this helps someone out there. I couldn’t find any workarounds on the web.