O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #99. Access SOAP Web Services from Excel

If your spreadsheet needs to access constantly updated data, or if you need to access services hosted on another computer, Excel's Web Services support will enable you to get connected.

SOAP-based Web Services have been a key part of Microsoft's plans for .NET, as well as a common feature of toolkits from other vendors. SOAP (the acronym doesn't mean anything) is a protocol that uses XML to transmit information between systems. In the case you'll explore here, it's used to call procedures and return values. A companion specification, Web Service Definition Language (WSDL), describes Web Services so that applications can connect to them easily. Microsoft's Web Services Reference Tool can take a WSDL file and generate VBA code your application can use to access SOAP-based web services.

Tip

This hack uses Excel features that are available only in Excel XP and Excel 2003 on Windows. Earlier versions of Excel do not support this, and neither do current or announced Macintosh versions of Excel.

Making this work requires downloading the Office Web Services Toolkit. As its location has changed a few times, it's easiest to go to http://www.microsoft.com/downloads/search.aspx and search for "Office Web Services Toolkit". Separate versions are available for Office XP and Office 2003. You'll need to install this toolkit, using the directions that come with it, before proceeding with this hack.

Once you've installed the toolkit, you can start ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required