Excel VBA：写入mysql数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2821718/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: writing to mysql database
提问by l--''''''---------''''''''''''
I would like to write a macro in Excel that will write to a mysql database. Can someone please get me started on this?
我想在 Excel 中编写一个宏来写入 mysql 数据库。有人可以让我开始吗？
回答by Fionnuala
You can connect to MySQL with a connection string and ADO:
您可以使用连接字符串和 ADO 连接到 MySQL：
''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:
您还可以通过 Jet 驱动程序将 DSN 与 Excel 连接使用：
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
回答by GSerg
Writing to a mysql database is no different to writing to any other database.
写入 mysql 数据库与写入任何其他数据库没有什么不同。
You'd create an ADODB.Connection object, .Open it with an appropriate connection stringand use the .Execute method (or ADODB.Command) to execute sql.
您将创建一个 ADODB.Connection 对象，使用适当的连接字符串打开它并使用 .Execute 方法（或 ADODB.Command）来执行 sql。
See http://msdn.microsoft.com/en-us/library/ms807027.aspxfor more information.
有关详细信息，请参阅http://msdn.microsoft.com/en-us/library/ms807027.aspx。
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.
您必须安装一个 mysql 访问驱动程序（ODBC 或 OLEDB）并从您的 vba 项目中引用 Microsoft ActiveX 数据对象 2.8。
