Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have created a SharePoint 2010 list with 2 relevant columns: - Identification Key - Version Number

I want to be able to query this SharePoint list from Excel VBA - passing the Identification Key to the query and retrieving the Version Number.

Since this list may end up having quite a few entries, I would prefer not to create an IQY connection to retrieve the data for searching.

share|improve this question
add comment

2 Answers 2

What version of Excel are you using? I have not tried it but believe you can make web service calls from Excel 2013 so should be able to access SharePoint list data through REST or SOAP calls.

share|improve this answer
    
You can just as easily create SOAP queries from Office 2010 applications using VBA, Excel 2013 is not a necessity. –  James Love May 18 '13 at 11:04
    
Ah - very nice! –  Dave Paylor May 18 '13 at 19:56
add comment

You can use VBA to talk to the SharePoint SOAP Web services, using the built in SoapClient class.

The web service you want to invoke is Lists.GetListItems - details here on MSDN: http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx - This page contains all the details you need to make a request, as well as the format of the response so hopefully you'll know how to parse it (if not, google/bing about for 'parsing an XML response form a web service in VBA', pretty sure it's done quite a lot already).

If you've no idea how to build a SOAP request in VBA, here's something I write many many moons ago that adds items to a SharePoint list when things are added to an Outlook Calendar - the important bits to take away and adapt to your requirements are the SoapClient bits.

Calling SharePoint from VBA Sample Code - http://sdrv.ms/12J0M4b

share|improve this answer
    
Thanks for the info - I will give it a try as soon as my current project gives me some breathing room. –  ArtB Jul 16 '13 at 16:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.