• Topic
  • Discussion
  • UdaWikiWeb.UseExcelForWebQueries(Last) -- DAVWikiAdmin? , 2021-11-05 13:08:03 Edit WebDAV System Administrator 2021-11-05 13:08:03

    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 (OpenLink Software) maintain & support, and which Apple has bundled into Mac OS X since Panther (10.3.x), up to and including the latest (as of this writing) Big Sur (11.5).

    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.

    1. 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.)

    2. 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.

    3. 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.

    4. 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...