vba 有没有办法让 ADODB 与 Excel for Mac 2011 一起使用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9707256/
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
Is there a way to get ADODB to work with Excel for Mac 2011?
提问by n8gard
I can not seem to get my Excel workbook (using ADODB) to work with Excel Mac 2011. I am using ADODB code. Are there add-ins available? Even from a third-party? Has anyone gotten this to work?
我似乎无法让我的 Excel 工作簿（使用 ADODB）与 Excel Mac 2011 一起使用。我使用的是 ADODB 代码。是否有可用的加载项？甚至来自第三方？有没有人让这个工作？
采纳答案by Siddharth Rout
Are there add-ins available? Even from a third-party?
是否有可用的加载项？甚至来自第三方？
Hope these download links helps?
希望这些下载链接有帮助吗？
ODBC drivers that are compatible with Excel for Mac
Quoted from the MSKB in case the link dies
引用自 MSKB 以防链接失效
ODBC drivers that are compatible with Excel for Mac
与 Excel for Mac 兼容的 ODBC 驱动程序
If you want to import data into Excel for Mac from a database, you need an Open Database Connectivity (ODBC)driver installed on your Mac. The driver you get depends on which version of Excel for Mac you have.
如果要将数据从数据库导入 Excel for Mac，则需要在 Mac 上安装开放式数据库连接 (ODBC)驱动程序。您获得的驱动程序取决于您拥有的 Excel for Mac 版本。
Excel for Mac 2011
Excel for Mac 2011
This version of Excel does not provide an ODBC driver. You must install it yourself. Drivers that are compatible with Excel for Mac 2011 are available from these companies:
此版本的 Excel 不提供 ODBC 驱动程序。您必须自己安装。可从以下公司获得与 Excel for Mac 2011 兼容的驱动程序：
After you install the driver for your source, you can use Microsoft Query to create new queries or refresh existing queries that were created in other versions of Excel, such as Excel X, Excel 2004, and Excel for Windows. For more information, see Import data from a database in Excel for Mac 2011.
为源安装驱动程序后，您可以使用 Microsoft Query 创建新查询或刷新在其他版本的 Excel（例如 Excel X、Excel 2004 和 Excel for Windows）中创建的现有查询。有关详细信息，请参阅从 Excel for Mac 2011 中的数据库导入数据。
Excel 2016 for Mac
Mac 版 Excel 2016
This version of Excel does provide an ODBC driver for connecting to SQL Server Databases. On the Datatab, click New Database Query> SQL Server ODBC. Then use the dialog boxes to import the data.
此版本的 Excel 确实提供了用于连接 SQL Server 数据库的 ODBC 驱动程序。在“数据”选项卡上，单击“新建数据库查询”>“ SQL Server ODBC”。然后使用对话框导入数据。
If you are connecting to other ODBC data sources (for example, FileMaker Pro), then you'll need to install the ODBC driver for the data source on your Mac. Drivers that are compatible with Excel for Mac are available from these companies:
如果您要连接到其他 ODBC 数据源（例如 FileMaker Pro），则需要在 Mac 上为数据源安装 ODBC 驱动程序。可从以下公司获得与 Excel for Mac 兼容的驱动程序：
Has anyone gotten this to work?
有没有人让这个工作？
Sorry, I have never used it.
抱歉，我从来没有用过。
回答by thedanotto
ADODB is NOT supported in Mac Excel 2011, but ODBC works in conjunction with a 3rd party driver.
Mac Excel 2011 不支持 ADODB，但 ODBC 与 3rd 方驱动程序一起使用。
I got my ODBC drivers from ActualTech. Download and install their program and you'll have the necessary drivers for connecting to SQL servers and databases (Free to try, $35 to purchase).
我从ActualTech获得了我的 ODBC 驱动程序。下载并安装他们的程序，您将拥有连接 SQL 服务器和数据库所需的驱动程序（免费试用，购买 35 美元）。
The following code creates a connection to a mySQL database, and returns information from the database into Cell A1:
以下代码创建到 mySQL 数据库的连接，并将信息从数据库返回到单元格 A1：
Dim connstring as String
Dim sqlstring as String
connstring = "ODBC;DRIVER={Actual Open Source Databases};" _
& "SERVER=<server_location>;DATABASE=<database>;" _
& "UID=<userID>;PWD=<password>;Port=3306"
sqlstring = "select * from <database_table>"
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
.BackgroundQuery = False
.Refresh
End With
回答by user2426679
There are 2 pieces to getting Mac Excel to query MySQL: (1) the VBA and (2) the ODBC driver.
让 Mac Excel 查询 MySQL 有两个部分：(1) VBA 和 (2) ODBC 驱动程序。
(1)The VBA for creating a table from a query (and updating it with a new query):
(1)用于从查询创建表（并使用新查询更新它）的 VBA：
Option Explicit
Sub WaitQueryTableRefresh(ByVal qt As QueryTable)
While qt.Refreshing
Application.Wait (Now + TimeValue("0:00:01"))
Wend
End Sub
Sub ErrorIfQueryTableOverflowed(ByVal qt As QueryTable)
If qt.FetchedRowOverflow Then
err.Raise 5, "ErrorIfQueryTableOverflowed", _
"QueryTable '" & qt.ListObject.Name & "' returned more rows than can fit in the spreadsheet range"
End If
End Sub
' Create a table from scratch
Function CreateTableFromSql( _
ByVal table_sheet As Worksheet, _
ByVal table_range As Range, _
ByVal table_name As String, _
ByVal sql As String _
) As ListObject
' table_range is simply the top-left, corner cell for the table
'ListObject.SourceType
'https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xllistobjectsourcetype?view=excel-pia
'QueryTable.CommandType
'https://docs.microsoft.com/en-us/office/vba/api/Excel.QueryTable.CommandType
'QueryTable.BackgroundQuery
'https://docs.microsoft.com/en-us/office/vba/api/excel.querytable.backgroundquery
'QueryTable.RefreshStyle
'https://docs.microsoft.com/en-us/office/vba/api/excel.xlcellinsertionmode
'QueryTable.PreserveColumnInfo
'https://stackoverflow.com/a/28621172
'https://docs.microsoft.com/en-us/office/vba/api/Excel.QueryTable.PreserveColumnInfo
Dim global_odbc_str As String
global_odbc_str = "ODBC;DSN=my_dsn_name;"
Dim qt As QueryTable
Set qt = table_sheet.ListObjects.Add( _
SourceType:=xlSrcExternal, _
Source:=global_odbc_str, _
Destination:=table_range _
).QueryTable
With qt
.ListObject.Name = table_name
.ListObject.DisplayName = table_name
.CommandText = sql
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With
Call WaitQueryTableRefresh(qt)
Call ErrorIfQueryTableOverflowed(qt)
Set CreateTableFromSql = qt.ListObject
End Function
' Update a table (columns do not have to be the same)
Sub UpdateTableFromSql( _
ByVal table As ListObject, _
ByVal sql As String _
)
Dim qt As QueryTable
Set qt = table.QueryTable
qt.CommandText = sql
qt.Refresh BackgroundQuery:=False
Call WaitQueryTableRefresh(qt)
Call ErrorIfQueryTableOverflowed(qt)
End Sub
(2)Configuring the MySQL ODBC driver (free)
（2）配置MySQL ODBC驱动（免费）
Install according to MySQL docs:
根据MySQL 文档安装：
(a)Install (dependency) iODBC Admin: http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads
(a)安装（依赖）iODBC Admin：http: //www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads
(b)Install MySQL ODBC driver: https://dev.mysql.com/downloads/connector/odbc/
(b)安装 MySQL ODBC 驱动程序：https: //dev.mysql.com/downloads/connector/odbc/
(c)Mac requires all apps (including Excel) to be quarantined to a sandbox. Because of this, you need to relocate the MySQL driver to a place where Excel can access it. The symptom of failing to do this is that the DSN connection will Testsuccessfully in iODBCbut will fail to Testin Excel's ODBC.
(c)Mac 要求将所有应用程序（包括 Excel）隔离到沙箱中。因此，您需要将 MySQL 驱动程序重新定位到 Excel 可以访问它的地方。未能做到这一点的症状是 DSN 连接将在 Excel 的 ODBC 中Test成功iODBC但将失败Test。
Relocate the driver according to this:
根据此重新定位驱动程序：
#!/bin/bash
# https://github.com/openlink/iODBC/issues/29
# https://bugs.mysql.com/bug.php?id=89931
base_src_dir="/usr/local"
mysql_odbc_name=$(ls "$base_src_dir" | grep -m 1 "mysql-connector-odbc")
odbc_dir="/Library/ODBC"
src="$base_src_dir/$mysql_odbc_name/lib"
dst="$odbc_dir/$mysql_odbc_name/lib"
echo "creating '$dst'"
sudo mkdir -p "$dst"
echo "copying '$src' to '$dst'"
sudo cp -af "$src/." "$dst"
odbc_ini_path="$odbc_dir/odbc.ini"
odbc_ini_bak_path="$odbc_ini_path.bak"
odbcinst_ini_path="$odbc_dir/odbcinst.ini"
odbcinst_ini_bak_path="$odbcinst_ini_path.bak"
echo "backing up '$odbc_ini_path' to '$odbc_ini_bak_path'"
sudo cp -f "$odbc_ini_path" "$odbc_ini_bak_path"
echo "backing up '$odbcinst_ini_path' to '$odbcinst_ini_bak_path'"
sudo cp -f "$odbcinst_ini_path" "$odbcinst_ini_bak_path"
# https://stackoverflow.com/a/29626460
function replace {
sudo sed -i '' "s/$(sed 's/[^^]/[&]/g; s/\^/\^/g' <<< "")/$(sed 's/[&/\]/\&/g' <<< "")/g" ""
}
ansi_driver=$(ls "$dst" | grep -m 1 "^lib.*a\.so$")
unicode_driver=$(ls "$dst" | grep -m 1 "^lib.*w\.so$")
old_ansi_path="$src/$ansi_driver"
new_ansi_path="$dst/$ansi_driver"
old_unicode_path="$src/$unicode_driver"
new_unicode_path="$dst/$unicode_driver"
echo "updating '$old_ansi_path' to '$new_ansi_path' in '$odbc_ini_path'"
replace "$old_ansi_path" "$new_ansi_path" "$odbc_ini_path"
echo "updating '$old_ansi_path' to '$new_ansi_path' in '$odbcinst_ini_path'"
replace "$old_ansi_path" "$new_ansi_path" "$odbcinst_ini_path"
echo "updating '$old_unicode_path' to '$new_unicode_path' in '$odbc_ini_path'"
replace "$old_unicode_path" "$new_unicode_path" "$odbc_ini_path"
echo "updating '$old_unicode_path' to '$new_unicode_path' in '$odbcinst_ini_path'"
replace "$old_unicode_path" "$new_unicode_path" "$odbcinst_ini_path"
The above was tested on High Sierra with Excel 2016.
以上是在 High Sierra 上用 Excel 2016 测试的。
