vba 如何将 ms excel-2007 与 mysql 连接。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25039573/
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
How to connect ms excel-2007 with mysql.
提问by Anil Chahal
How to connect excel-2007 with mysql. I am on windows 7, 64 bit. Using excel-2007 32 bit and mysql version 5.1 , 32 bit. Can Anyone help me how to import my mysql table data in excel file. Thanks in advance.
如何连接 excel-2007 和 mysql。我在 Windows 7 上，64 位。使用 excel-2007 32 位和 mysql 5.1 版，32 位。任何人都可以帮助我如何在excel文件中导入我的mysql表数据。提前致谢。
回答by Anil Chahal
To connect your excel-2007 (or any version) with your mysql you need ODBC driver. Please download correct ODBC driver from the authenticated link
要将您的 excel-2007（或任何版本）与您的 mysql 连接，您需要 ODBC 驱动程序。请从经过身份验证的链接下载正确的 ODBC 驱动程序
http://dev.mysql.com/downloads/connector/odbc/
http://dev.mysql.com/downloads/connector/odbc/
Please ensure you download the correct .msi or zip file according to your computer bit architecture. You need to ensure your Mysql running on 32 bit or 64 bit. Please follow this link to check
请确保根据您的计算机位架构下载正确的 .msi 或 zip 文件。您需要确保您的 Mysql 运行在 32 位或 64 位上。请点击此链接查看
MySQL: check what version : 32 bit or 64 bit?
Now after that please ensure your excel-2007 is 32 bit or 64 bit. For this open excel file and start the task manager and check your bit architecture under Processes option. 
现在之后请确保您的 excel-2007 是 32 位或 64 位。对于这个打开的 excel 文件并启动任务管理器并在 Processes 选项下检查您的位架构。
In my case, i am using windows7 64 bit, excel is 32 bit, mysql is also 32 bit
就我而言，我使用的是 windows7 64 位，excel 是 32 位，mysql 也是 32 位
So i downloaded Windows (x86, 32-bit), MSI Installer from this link
所以我从这个链接下载了 Windows (x86, 32-bit), MSI Installer
http://dev.mysql.com/downloads/connector/odbc/
http://dev.mysql.com/downloads/connector/odbc/
Now very important step : In order to use a 32 bit ODBC driver on a 64 bit Windows machine, it has to be configured using a 32 bit ODBC Administrator which is not visible through normal Windows menu options. Utilities for 32 bit operations are found in the C:\Windows\SysWOW64 directory. We need to add the SysWOW64 directory to the system path, as follows
现在非常重要的一步：为了在 64 位 Windows 机器上使用 32 位 ODBC 驱动程序，它必须使用 32 位 ODBC 管理器进行配置，这在正常的 Windows 菜单选项中是不可见的。用于 32 位操作的实用程序位于 C:\Windows\SysWOW64 目录中。我们需要在系统路径中添加SysWOW64目录，如下
1. Click on start menu, right Click on My Computer -> Properties -> Advanced system settings
2. Choose Advanced Tab -> Environmental Variables -> System Variables
3. Select and edit the PATH variable. Prefix the PATH list with 'C:\Windows\SysWOW64;'!
As of now correct path is set for installed ODBC Driver, now we need to create DSN (Data Source Name) for linking between excel and mysql.
Now Open Control Panel and click on the Administrative Tools menu item to begin the installation of a new data source. Select Data Source ODBC , This is where you add a new User Data Source. Click the Add button on the right of the dialog box.
Now after pressing add button if you are able to see your Installed Driver in New Create Data Source pop up like this
现在为安装的 ODBC 驱动程序设置了正确的路径，现在我们需要创建 DSN（数据源名称）以在 excel 和 mysql 之间进行链接。现在打开控制面板并单击管理工具菜单项开始安装新的数据源。选择 Data Source ODBC ，这是您添加新用户数据源的地方。单击对话框右侧的添加按钮。现在按下添加按钮后，如果您能够在 New Create Data Source 中看到安装的驱动程序会像这样弹出
then its good if you follow this link for linkage between excel and mysql.
那么如果您按照此链接进行 excel 和 mysql 之间的链接，那就太好了。
http://blog.mclaughlinsoftware.com/microsoft-excel/accessing-to-mysql/
http://blog.mclaughlinsoftware.com/microsoft-excel/accessing-to-mysql/
If you not able to find your installed driver and pop up window for New Create Data Source looks like this
如果您找不到已安装的驱动程序并弹出新建数据源的窗口如下所示
Need not to panic if your installed driver not listed over there. Access the ODBC administrator utility located at 'C:\Windows\SysWOW64\odbcad32.exe'.
如果您安装的驱动程序未在此处列出，请不要惊慌。访问位于“C:\Windows\SysWOW64\odbcad32.exe”的 ODBC 管理员实用程序。
Search for odbcad32.exe in 'C:\Windows\SysWOW64' directory
在“C:\Windows\SysWOW64”目录中搜索odbcad32.exe
Now double click on second option of search result "odbcad32"
现在双击搜索结果“odbcad32”的第二个选项
Now click on add button and select MySQL ODBC 5.3 ANSI Driver, click finish
现在单击添加按钮并选择 MySQL ODBC 5.3 ANSI 驱动程序，单击完成
In pop up window enter your details e.g. Data Source Name = MySQLExcel
Description = MySQL Excel Connector/ODBC
Server = localhost
Username and password of mysql, and name of the database.
Click on Test if everything is ok then you got a confirmation "Connection Successful"
如果一切正常，请单击测试，然后您会收到确认“连接成功”
After the test, you return to the first screen where you should see that you've added a MySQLExcel Data Source Name. Click the OK button to dismiss the affirmation of the test.
测试后，您将返回到第一个屏幕，您应该会看到您已添加 MySQLExcel 数据源名称。单击“确定”按钮取消对测试的确认。
Now to set up data importing from mysql to excel, Open excel, click on the Data Ribbon. Click the From Other Sources button. In the drop down menu, click on the From Data Connection Wizard item, which launches the Data Connection Wizard.
现在要设置从 mysql 到 excel 的数据导入，打开 excel，单击数据功能区。单击来自其他来源按钮。在下拉菜单中，单击从数据连接向导项，这将启动数据连接向导。
Select Other/Advanced item from the list of data sources. Click the Next button to continue.
从数据源列表中选择其他/高级项目。单击下一步按钮继续。
Choose the Microsoft OLE DB Provider for ODBC Drivers item from the list of OLE DB Provider(s). Click the Next button to continue.
从 OLE DB 提供程序列表中选择 Microsoft OLE DB Provider for ODBC Drivers 项。单击下一步按钮继续。
Choose the Connection tab in the Data Link Properties dialog. Under item #1, choose the Data Source Name (DSN) from the drop down box (set in a previous statement). Under item #2, enter the user name and password for mysql. Under item #3, select database.
在“数据链接属性”对话框中选择“连接”选项卡。在项目 #1 下，从下拉框中选择数据源名称 (DSN)（在前面的语句中设置）。在第 2 项下，输入 mysql 的用户名和密码。在第 3 项下，选择数据库。
Click on test connection to ensure everything is ok.
单击测试连接以确保一切正常。
Then, click the OK button on the Data Link Properties dialog.
然后，单击“数据链接属性”对话框上的“确定”按钮。
Select the table name and click on next button
选择表名，然后单击下一步按钮
Now click on finish button. The Data Import Wizard is complete with the last step. Now, you must import the data into the existing worksheet or a new worksheet.
现在点击完成按钮。数据导入向导完成最后一步。现在，您必须将数据导入现有工作表或新工作表。
Now press ok button. your mysql data will be there in excel file. Final response looks like this.
现在按确定按钮。您的 mysql 数据将存在于 excel 文件中。最终响应如下所示。
回答by asif
I am adding to @Anil Chahal answer:
我正在添加@Anil Chahal 的回答：
After configuration as described by @Anil Chahal, following code can be used to fetch data from MySql into Excel.
按照@Anil Chahal 的描述进行配置后，可以使用以下代码将数据从 MySql 提取到 Excel 中。
Function runQuery()
Dim cn As Object
Dim rs As Object
Dim strSql As String
Dim strConnection As String
Set cn = CreateObject("ADODB.Connection")
'Set your DB particulars
strConnection = "Data Source=MySQLExcel;Driver={MySQL ODBC 5.5.25a Driver};Server=" & _
"localhost" & ";Database=" & "your-db-name" & _
";Uid=" & "your-user-name" & ";Pwd=" & "your-password" & ";"
cn.Open strConnection
'Set your MySql query, i used "Select" query
strSql = "SELECT * from Table-Name;"
Set rs = cn.Execute(strSql)
'In case of "Select query" set your range to show records
'In case of "insert/edit/delete query" exclude next two lines
Worksheets("SearchResults").Range("a4:xfd1048576").ClearContents
Range("b4").CopyFromRecordset rs
'close the connection
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Function
结束函数
回答by Mike Powell
I use DAO to connect between Excel (2007) and my database (typically MS Access).
我使用 DAO 在 Excel (2007) 和我的数据库（通常是 MS Access）之间进行连接。
To do this, you need to:
为此，您需要：
Add Microsoft DAO 3.6 Object Library to your VBAProject References (i.e. Tools ─? References).
将 Microsoft DAO 3.6 对象库添加到您的 VBAProject 引用（即工具─？引用）。
code would look something like this:
代码看起来像这样：
Sub MySub()
Set Db = OpenDatabase("C:\MyDB.mdb")
Set rst = Db.OpenRecordset(" SELECT * FROM MyTable")
' output
Sheets.Add
With ActiveSheet.Cells(2, 1)
rec_QTY = .CopyFromRecordset(rst)
End With
rst.Close
Db.Close
End Sub
Or if you want to execute a specific command:
或者，如果要执行特定命令：
Sub MySub_single_command()
Set Db = OpenDatabase("C:\MyDB.mdb")
Db.Execute (" DELETE * FROM MyTable;")
Db.Close
End Sub
cheers, Mike
干杯，迈克
