Utilizing Sharepoint Web Services from Excel VBA

[Sajan]

Hi,

Does anyone have any sample Excel VBA code that shows how to use the Sharepoint Web Services? I did find some cryptic examples elsewhere, but before I embarked on interpreting what I found into VBA, I was hoping that someone may have translated it to VBA already!


I am specifically looking to use the "lists" web service.


For example, the following will read a Sharepoint list, and populate a worksheet:

[pre]

Sub ExtractSharepointList()
Const strViewGUID = "56d8b0f4-57af-4e26-ac10-42e006deaf99"
Const strServerName = "http://myserver.mycompany.com/sites/_vti_bin"
Const strListName = "{6bab29c0-d2d0-4124-9e95-0aa0164da175}"
Dim lst As ListObjects
Dim lstobj As ListObject
Dim wsheet As Worksheet

Set wsheet = ActiveWorkbook.Worksheets.Add
Set lst = wsheet.ListObjects
Set lstobj = lst.Add(SourceType:=xlSrcExternal, Source:=Array(strServerName, strListName, strViewGUID), LinkSource:=False, _
TableStyleName:=xlGuess, Destination:=Range("A1"))
End Sub

[/pre]
However, I am hoping to be able to read a Sharepoint list without populating a worksheet so that I can access all fields available in the list through the code. (For example, I am looking to determine previous versions of a field, etc.)


I am using Sharepoint 2010 with Excel 2010. (I do not have access to Sharepoint designer. As such, the code would need to be supported from the client side.)


Any help or pointers would be very much appreciated.


Thanks,

Sajan.