CODEWITHSUNDEEP
mysqlsqlexcelvba
Alex Gordon
Alex Gordon

Reputation: 60691

Excel VBA: writing to mysql database

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

Upvotes: 2

Views: 14271

Answers (2)

Fionnuala
Fionnuala

Reputation: 91316

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

Upvotes: 8

GSerg
GSerg

Reputation: 78144

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.

Upvotes: 3

Related Questions