excel mysql访问数据库 recordset,Excel VBA：写入mysql数据库

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

解决方案

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