2

I would like to write a macro in Excel that will write to a mysql database. Can someone please get me started on this?

Community
  • 1
  • 1
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062

2 Answers2

8

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
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • thanks remou, can u show me how i would execute the sql statement>? – Alex Gordon May 12 '10 at 19:04
  • 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. – Fionnuala May 12 '10 at 19:17
  • @Remou: Hey I get the error : `Compiler Error: User-defined types not defined` on line `Dim cn As ADODB.Connection` What could be wrong? – Parth Bhatt Mar 18 '11 at 07:19
  • @Parth Add a reference to Microsoft ActiveX Data Objects x.x Library - from the code window choose Tools->References and tick the relevant line. – Fionnuala Mar 18 '11 at 09:20
  • Thanks Remou for your quick reply. What is the `DSNName` and `NameOfMySQLField`? What should I write in place of that? – Parth Bhatt Mar 18 '11 at 10:05
  • @PARTH If you have a DSN (http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-dsn-windows.html), it DSNName the name you gave that, if not the whole bracketed section `[ODBC;DSN=DSNName;]` can contain a suitable connection string (http://connectionstrings.com). NameOfMySQLField is just that, the name of the relevant field in your MySQL table. – Fionnuala Mar 18 '11 at 11:20
  • 1
    +1 `strSQL = "INSERT INTO [ODBC;DSN=DSNName;].NameOfMySQLTable " _ & "Select AnyField As NameOfMySQLField FROM [Sheet1$];"` this is _slick_ ... i never knew this. – transistor1 Aug 24 '11 at 01:31
3

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.

GSerg
  • 76,472
  • 17
  • 159
  • 346