I ran into a problem linking Visio shapes to Excel data the other day. If you are running 64-bit versions of Windows and Microsoft Office, you might run into the same problem.
Fortunately, the solution was quite easy!
Provider Problem Prelude
My Lenovo ThinkPad T410 runs Windows 7 Professional 64-bit, and I have Visio Premium 2010 64-bit installed as well.
The “64-bit-ness” seemed to be part of the problem. When I tested on a Virtual PC running 32-Bit Windows 7 Ultimate, and 32-bit Office/Visio 2010, the data-linking worked fine. Anyway…
I started with a very simple Excel 2010 file that had just a few equipment items with their associated list of ports:
click to view larger image
I opened Visio’s External Data Window, clicked Link Data to Shapes, which started the Data Selector wizard.
click to view larger image
I browsed to the Excel file expecting all to go smoothly, when POW! I got this error message: “The selected file is not a Microsoft Excel file or is currently inaccessible.”
Well, we just saw that it most certainly IS an Excel file, so that didn’t make any sense!
Expanding the error expanded showed a bit more info: “Error 800a0e7a: Provider cannot be found. It may not be properly installed.”:
Maybe plain English to some of you, but Greek to me!
Solution? Download and Install Something!
I poked around a little bit, then thought about the word “Provider” in the expanded error message. I know that data drivers or data providers allow all these apps to communicate with each other, but I don’t really know–or care–about details. At any rate, I figured there must be something new.
And there appears to be! The trick was to install the brand-spanking-new Microsoft Access Database Engine 2010 Redistributable:
Make sure you get the 64-bit version!
Thereafter, I was able to open the Excel file in the External Data window and link data to my shapes:
click to view larger image
The new engine allowed Visio to extract data from Excel files.
So What’s Going On? Chime In, Please!
I really don’t know where the weak link is. Do default installs of 64-bit Windows 7 and 64-bit Office & Visio 2010 work just fine regarding data-linking, and I had somehow gummed up my system? Do Microsoft’s default installs really overlook this problem and leave you with non-functioning Visio-Excel communication?
If you know more about this, please leave a comment below so we can better understand the problem!
Fellow Visio MVP Al Edlund tried to shed some light on the subject for me, but it was a bit over my head:
The issue of moving data-linking between versions can get interesting on a couple of levels. The first of course is the ADODB drivers involved (a real change between v2003 and v2007) and another between the MMC utilities (Microsoft Management Console–different between x32 and x64, with the default being the system you are on – not the application you are trying to control).
I also found this article: Accessing Excel files on a x64 machine, which seems to be related. But I still don’t fully understand the root of the problem.
The non-techie answer is that Visio does not launch the Excel application and go through the Excel API to get data from a spreadsheet. Rather Visio uses the Access Database Engine to treat the Excel spreadsheet like a data source.
Visio does not have a specific error message for this 32/64 bit mismatch condition and shows a more generic error. In fact only the first error message comes from Visio. The error message under More Info comes from the Access Database Engine.
I went back to 32-bit. Some programs didn’t seem to like Windows (32 bit) on Windows (64 bit) (WOW) and Office 2010 x64 doesn’t support Windows Mobile Device Center, so you can’t sync your phone to your desktop.
W7 x86 has been rock-solid and even though x64 was a little faster in a few places, I’ll take the reliability.
Go 4 German Beer!
Error message is illogical but wonderful solution!