Using Microsoft Excel's Web Query functionality on Mac OS X
You may have seen an error message like
To refresh a query, you must first install the appropriate ODBC driver for this version of Excel on your computer. For information about obtaining ODBC drivers, visit Microsoft website at http://microsoft.com/mac/.
Microsoft's knowledgebase suggests a need a for ODBC drivers to enable Web Query and other functionality in Excel on Mac, but this is not accurate.
Microsoft Excel (and more accurately Microsoft Query) do depend on iODBC library files.
iODBC is the free and open source ODBC driver manager on Mac OS X, which we (
Unfortunately, Apple has always lagged behind updates made to the iODBC project -- today, Apple is shipping iODBC 3.52.6 (October, 2007) in Big Sur, even though iODBC has been updated several times, to deliver compatibility with various macOS updates (see the iODBC change log) —
iODBC version | iODBC version release date | updated for macOS version(s) | compatible with macOS version(s) | processor architectures |
---|---|---|---|---|
3.52.7 | September, 2009 | Lion (10.7.x) | Lion (10.7.x) | x86_32, x86_64 |
3.52.8 | March, 2012 | Mountain Lion (10.8.x) | Lion (10.7.x) through Mountain Lion (10.8.x) | x86_32, x86_64 |
3.52.9 | April, 2014 | Mavericks (10.9.x) | Lion (10.7.x) through Mavericks (10.9.x) | x86_32, x86_64 |
3.52.10 | February, 2015 | Yosemite (10.10.x) | Lion (10.7.x) through Yosemite (10.10.x) | x86_32, x86_64 |
3.52.12 | June, 2016 | El Capitan (10.11.x) | Lion (10.7.x) through Big Sur (11.x) | x86_32, x86_64 |
3.52.13 | July, 2019 | Catalina (10.15.x) | Lion (10.7.x) through Big Sur (11.x) | x86_32, x86_64 |
3.52.14 | February, 2021 | Big Sur (11.x) | Lion (10.7.x) through Big Sur (11.x) | x86_32, x86_64 |
3.52.15 | June, 2021 | Big Sur (11.x) | built for Mavericks (10.9.x) through Catalina (10.15.x) or for Mojave (10.14.x) through Big Sur (11.x) |
built for x86_32 and x86_64 or for x86_64 and arm64 |
Also troublesome, Apple bundles only "dylib" files, while Microsoft and others depend on "Frameworks" versions of these libraries.
Enabling Web Queries in Microsoft Excel
This takes just a few steps on your Mac.
- Update Microsoft Office, which you can do through the Excel HELP menu, "Check for Updates." Repeat this step until no more updates are found.
(Office 2011 14.1.2 is the earliest version compatible with OS X Lion or later.
We recommend applying all available Office updates, for both Office 2011 and Office 2016.)
- Download the latest iODBC installer which requires no license to function.
There is no cost for this installation nor for use of the iODBC components.
- Follow one of these guides to create Web Query files for Excel.
The same instructions are applicable to all versions of Excel for Mac OS X.
- Microsoft Knowledgebase guide
(no longer on Microsoft's own site; here's an Archive.org capture)
- Third-party user guide
- Microsoft Knowledgebase guide
- Tell Microsoft that their documentation needs updating, to say that only the iODBC Frameworks are needed for such Web Query tasks, not any ODBC Drivers.
Referenced by...