I would like to write a macro in Excel that will write to a mysql database. Can someone please get me started on this?
Add a comment
|
2 Answers
You can connect to MySQL with a connection string and ADO:
''http://support.microsoft.com/kb/246335
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strCon = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=MyDB;" _
& "User=root;Password=pw;Option=3;"
cn.Open strCon
You can also use DSN with a connection to Excel using the Jet driver:
Dim cn As ADODB.Connection
''Not the best way to get the name, just convenient for notes
strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
''For this to work, you must create a DSN and use the name in place of
''DSNName
strSQL = "INSERT INTO [ODBC;DSN=DSNName;].NameOfMySQLTable " _
& "Select AnyField As NameOfMySQLField FROM [Sheet1$];"
cn.Execute strSQL
7 Comments
Alex Gordon
thanks remou, can u show me how i would execute the sql statement>?
Fionnuala
I have added some notes, but I do not have MySQL installed on this PC at the moments, so I am working from my notes. The above suggestions are by no means the only ways to go about this.
Parth Bhatt
@Remou: Hey I get the error :
Compiler Error: User-defined types not defined on line Dim cn As ADODB.Connection What could be wrong?Fionnuala
@Parth Add a reference to Microsoft ActiveX Data Objects x.x Library - from the code window choose Tools->References and tick the relevant line.
transistor1
+1
strSQL = "INSERT INTO [ODBC;DSN=DSNName;].NameOfMySQLTable " _ & "Select AnyField As NameOfMySQLField FROM [Sheet1$];" this is slick ... i never knew this. |
Writing to a mysql database is no different to writing to any other database.
You'd create an ADODB.Connection object, .Open it with an appropriate connection string and use the .Execute method (or ADODB.Command) to execute sql.
See http://msdn.microsoft.com/en-us/library/ms807027.aspx for more information.
You'd have to a have a mysql access driver installed (ODBC or OLEDB) and reference the Microsoft ActiveX Data Objects 2.8 from your vba project.
