Rectangle 27 1

calling web service using VBA code in excel 2010?


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).

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.

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

Wow. Great piece of work, have not tested it yet, but looks awesome. Thanks a lot!

Note
Rectangle 27 1

calling web service using VBA code in excel 2010?


Fair enough. In my opinion MS would like to have VBA dead and it is pushing .net solutions for Office. On the seperate note, you don't have to use Visual Studio. You can use NotePad and command line compiler.

Funny, I am working for one of those and they would be very happy with removing any code from an Excel binary file. Some of them have even done that; they have VBA modules stored in a code repository and a workbook is rebuilt from those for each release.

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.

One would be surprised about the number of banks (even very large) that would nearly stop working if VBA disappeared tomorrow !!!

Note
Rectangle 27 1

calling web service using VBA code in excel 2010?


FWIW as of the time of posting I've found that the old Web References Toolkit of Excel 2003 still produces working VBA code in Excel 2007/10 (note that there have been some shaky moments with various Windows updates over the last 18 months that has stopped this code working so I wouldn't consider my solution 'reliable').

My horrible hack involves installing Excel 2003, then the Web References Toolkit, then installing Excel 2007 or Excel 2010. I've set up 3 PCs using this hack and all are producing working VBA code to consume asmx web services (I haven't tried connecting with other types of web service but I don't see why they wouldn't work).

The official MS method, using VSTO, is a step too far for some of our clients and this VBA hack has kept them happy.

Note
Rectangle 27 1

calling web service using VBA code in excel 2010?


FWIW as of the time of posting I've found that the old Web References Toolkit of Excel 2003 still produces working VBA code in Excel 2007/10 (note that there have been some shaky moments with various Windows updates over the last 18 months that has stopped this code working so I wouldn't consider my solution 'reliable').

My horrible hack involves installing Excel 2003, then the Web References Toolkit, then installing Excel 2007 or Excel 2010. I've set up 3 PCs using this hack and all are producing working VBA code to consume asmx web services (I haven't tried connecting with other types of web service but I don't see why they wouldn't work).

The official MS method, using VSTO, is a step too far for some of our clients and this VBA hack has kept them happy.

Note
Rectangle 27 1

calling web service using VBA code in excel 2010?


A few years later...I found this page which is the best and clearest explanation and example I have found so far, including an interesting link providing services to play with.

Note
Rectangle 27 1

calling web service using VBA code in excel 2010?


Windows Communication Foundation (WCF) service moniker to integrate Web services into COM-based development environments, such as Microsoft Office Visual Basic for Applications (Office VBA) or Visual Basic 6.0. For detail see link https://docs.microsoft.com/en-us/dotnet/framework/wcf/samples/using-the-wcf-moniker-with-com-clients

Note
Rectangle 27 0

calling web service using VBA code in excel 2010?


Fair enough. In my opinion MS would like to have VBA dead and it is pushing .net solutions for Office. On the seperate note, you don't have to use Visual Studio. You can use NotePad and command line compiler.

Funny, I am working for one of those and they would be very happy with removing any code from an Excel binary file. Some of them have even done that; they have VBA modules stored in a code repository and a workbook is rebuilt from those for each release.

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.

One would be surprised about the number of banks (even very large) that would nearly stop working if VBA disappeared tomorrow !!!

Note
Rectangle 27 0

calling web service using VBA code in excel 2010?


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).

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.

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

Wow. Great piece of work, have not tested it yet, but looks awesome. Thanks a lot!

Note