So appears that’s the ODBC drivers in the click-to-run Office 2016 doesn’t install properly or it’s possible it’s incorrectly setup by default. I haven’t figured that part out yet but it was causing one of my clients trouble.

They were getting the following error when refreshing the data in some of their spreadsheets after their Click-to-Run O365 installs updated to Office 2016 from Office 2013.

After some back and forth they figured out it was the data being pulled from other Excel sheets wasn’t updating and not the data from their custom order management system which seemed to be working fine.

Digging around the internet showed a lot of suggestions to remove and re-add the Excel DSN. However trying to remove the DSN presented with further errors suggesting I should reinstall the Excel ODBC drivers.

Looking for a current version driver proved difficult but eventually I discovered installing the Microsoft Access 2016 Runtime should do it. (Access and Excel ODBC drivers are apparently related). However after downloading the x86 version of the run time (you need to match your office install architecture). It warned me that it was it did not play nicely with the Click-to-Run Office installs I had. Back to the drawing board.

Playing around with the ODBC administration CPL some more, I eventually got it to generate another error when I tried to add the currently installed ver 16 Office ODBC Excel driver.

Googing the error I found this helpful page which gave me a hint of what I could try next. (I stole their image because I forgot to screenshot my error before fixing it.) I could try installing an older driver for Office 2010. To be exact the Microsoft Access Database Engine 2010 Redistributable which installs version 14 of the ODBC drivers for Excel and Access. This package has no conflicts with Click-to-run.

Now that all the errors removing and adding DSNs had gone away, I removed the User DSN for Excel Files and re added it as System DNS for Excel Files using the version 14 driver. (System DSNs are only modifiable by the administrator.)

With that done, I tested the spreadsheets once more and finally, no more errors refreshing data. Now I can go back to sleep.

Something to note: It seems that the default Data Sources CPL only shows and configures 64-bit DSNs on a 64-bit Windows 7 install. If you want to see and configure the 32-bit DSNs (which you would want to for supporting legacy processes), you have to run the 32-bit ODBC administrator CPL found here:

C:\%windir%\SysWOW64\odbcad32.exe

This shows both 32-bit and 64-bit DSNs and more importantly you can add and configure 32-bit DSN. Remember to run it as Administrator or you won’t be able to touch the System DSNs.