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