Calling Web Services within Microsoft Office via the SOAP protocol in the year 2013

In the early to mid-2000s Microsoft Corp. painted the world a beautiful picture of the ease at which one could exchange information across disparate systems, whether across the LAN or across the Internet.  The company simultaneously eased both Office users and professional developers into unfamiliar terminology like Web Services, Simple Object Access Protocol (SOAP), and XML.

A bubble by Jeff Kubina is licensed under Creative Commons | Attribution-Share Alike 2.0 Generic
A bubble by Jeff Kubina is licensed under Creative Commons | Attribution-Share Alike 2.0 Generic

Don’t worry about the new terminology, Microsoft told Office users (paraphrased).  With a few drag-and-drops and mouse clicks, we made it simple for you to call a web service with little to no coding experience.  You’re going to love how easy it is to push and pull data using Visual Basic for Applications (VBA) within your Office files—namely Excel (paraphrased).

And then there were the professional developers.

Don’t worry about the new terminology, the company told us (paraphrased).  With a few drag-and-drops and mouse clicks, we made it simple for you to create your own web service.  You’re going to love how easy it is to expose your application’s properties and methods so that others can push and pull data from your system (paraphrased).

At the time, Microsoft’s vision was truly spot-on.  An Office user who wanted to call a public (non-authenticated) or private (authenticated) web service from within an Office document simply needed to download and install a free Microsoft Office Add-In, Microsoft Office XP Web Services Toolkit 1.0 (for Office XP), or Microsoft Office XP Web Services Toolkit 2.0 (for Office XP), or Microsoft Office 2003 Web Services Toolkit 2.01 (for Office 2003).

Writing the code to consume a web service within an Office file was analogous to installing a modern gas stove.  It really wasn’t really programming, but more like making a connection or two like the flexible gas hose and AC power cord.  If you were slightly handy and could follow directions, within an hour or so you’d be up and running.

Most importantly, it was brilliantly-simple to share one’s documents with others.  There was no application be to packaged, distributed, and installed.  Other than Office, no additional software was required on client machines, not even the toolkit itself.  That’s because the required library file to make the technology work, MSSOAP30.DLL, was automatically installed by default with Office.  And, if as a professional developer you wanted to create your own web service, it was only slightly more difficult to expose public and private data to the world using a more sophisticated software development tool, Microsoft’s Visual Studio.

So what do you think happened next?  That’s right, people adopted this wiz-bang technology!  Professional developers started writing their systems to expose data and Office users started to consume data within their Office files.

All good things must come to an end (for the Office user)

For a short while life was good.  Housing prices in the US skyrocketed.  Starbucks shops appeared in neighborhoods worldwide.  But Microsoft had different plans for the Office user.  While very little changed for professional developers in the way they created web services, it became more and more difficult to consume web services from Office files due to an increase in variability amongst client computing in industries across the world:

  • Microsoft introduced 32-bit and 64-bit varieties of Windows Vista;  few companies adopted
  • Microsoft introduced 32-bit and 64-bit varieties of Windows 7; slow company adoption
  • Microsoft introduced 32-bit variety of Office 2007 with brand new UI; few companies adopted
  • Microsoft announces it’s Web Services Toolkit is obsolete due to .NET technology
  • Microsoft stops support of Microsoft Office 2003 Web Services Toolkit 2.01 in 2008
  • Microsoft introduced 32-bit and 64-bit varieties of Office 2010; slow company adoption

Today, browsing the Internet for information on how to call a web service from Office files conjures imagery not unlike parts of Ukraine that surround the Chernobyl Nuclear Power Plant.  Newsgroups and forums display remnants of what was once a thriving community—now nearly abandoned—with questions and answers time-stamped from 2004 through 2009.  Well-intentioned, but ill-informed newsgroup participants (including Microsoft’s own forum administrators) point users to obsolete web service toolkits, even though those posting in forums clearly state they’re using Office 2007 or 2010.  Some thriving vagabonds even propose wild and unimaginable solutions for handling and parsing your own XML without the need for a Web Service Toolkit, which I admit is doable, albeit cumbersome for most but the seasoned developer.

For me though, the coup de grâce is that Microsoft casually recommends the newest solution to accomplish the task through an Office file project within Visual Studio—leveraging managed code—using .NET technology.  What’s discouraging in this approach, however, is that those posting on behalf of Microsoft don’t acknowledge, recognize, or understand the unique business challenges that approach brings, namely now requiring compilation and deployment of projects using Windows-installed packages.  Gone are the days of simply sharing an .XLS file with another Office user like the good old days.

As corporations now embrace Windows 7 and Office 2007 and Office 2010, it’s becoming more difficult for Office users to maintain and update their existing legacy Office files that consume web services, let alone develop new solutions.  Office 2003 is phasing out of corporate environments.  References to the MSSOAP30.DLL file are breaking as end-user machines are migrated to 64-bit Windows 7 and 32-bit or 64-bit Office 2007 and 2010.

Hope for the future

There’s a glimmer of hope that Microsoft identified it’s faux pas.  For an unsupported, obsolete technology, Microsoft Office 2010 installs an updated version of the MSSOAP30.DLL file.  That’s interesting.  Also, early indications show that Office Excel 2013 will include three built-in formulas that allow for calling web services:  EncodeURL(), Webservice(), and FilterXML().

In the meantime – scenarios and workarounds

Some Office users may have it easier than others depending on whether their web service project is used by one individual, one department or company, or whether distributed to other companies and clients.  But all Office users are on borrowed time.

If you want to make a new connection to a web service within your project today, you’re going to need development machine with Office 2003 installed so that you may also install Microsoft Office 2003 Web Services Toolkit 2.01.  Otherwise, you’re going to have handle and parse your own XML.

You’re in a bit better shape if you simply want to modify your projects but not necessarily add, edit, or remove existing connections to a web service.  Although your project can be opened in Office 2007 and Office 2010, your Office 2003 file will need to be opened in Compatibility Mode and will need to reference the MSSOAP30.DLL in the correct shared folder path which changes with each subsequent version of Office and the 32-bit vs. 64-bit version of Windows.  You’ll probably need to get creative with your code, namely when your file opens, in order to programmatically select the path to the SMSSOAP30.DLL depending on the versions of Windows and Office installed.

Finally, if you have a project deployed to a large number of client machines that have since upgraded to newer versions of Windows and/or Office, you may have luck by simply copying/pasting the currently-registered version of MSSOAP30.DLL file into a directory path that mimics that of an older version of Office.  You would do this if your project is set to reference MSSOAP30.DLL in a particular folder, or if you did not programmatically set the path to the MSSOAP30.DLL within your project.  I found this to be successful when our organization migrated from Windows XP and Office 2003 to Windows 7 and Office 2010.  Just remember that C:Program Files folder is not the same as C:Program Files (x86).

Summary

I re-posted this article I wrote in 2013 with the hope that it helps save others some troubleshooting time in the future.  Ten years have passed since I was a full-time software developer but I know that legacy code exists in the modern office environment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s