Calling web service using VBA code in Excel 2010

Question

I am trying to write some VBA code in Excel 2010 that would consume a web service. I am unable to find any related resources on the internet. Can someone please tell me how to do this.

Answer 1

I struggled with the same question on a recent project of mine where I wanted to talk to Trello and Salesforce in pure VBA without installing any plugins, opening up Visual Studio, or other hacks. Ended up writing my own library (based heavily on one of my favorites, RestSharp).

Warning, shameless plug: https://github.com/VBA-tools/VBA-Web

Some fun features include Mac support(!), authentication (Http Basic, OAuth1, OAuth2, and others), Async support, and JSON parsing (thanks to VBA-JSON)

It's working awesomely in Excel 2010 and 2013 (and most likely 2007) and I've got it working with Salesforce, Trello, Basecamp, Google Maps, and it should work with pretty much any webservice.

Answer 2

I don't think the post marked as the answer is right - it links to the Excel 2003 solution, which was based on the MS Office Web Services Toolkit. That solution is no longer valid as the Toolkit is not supported any more. See: MSDN - Consuming Web Services in Excel 2007. The link provides a solution for Excel 2007 which can be transposed to Excel 2010. You will need Visual Studio 2010 with Microsoft Office Developers Tools though. It also means utilising .Net Framework and coding in C# or VB.NET.