Link to home
Start Free TrialLog in
Avatar of Russellbrown
RussellbrownFlag for United States of America

asked on

Retrieve the GUIDs for lists from the Sharepoint


I need help to write an excel vba routine to retrieve GUIDs for all lists fron Sharepoint.

I am using Excel 2010 and Share Point 2010.

The lists in question will be located in the "Lists" subfolder of Sharepoint.

Thank you in advance for your kind assistance.
Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image


you should use a call to the lists web service using the function GetListCollection which will return a collection of list names and guids.
Code sample:

Calling Webservice from within Excel:

Just set the path to the list service to (if you want to retrieve lists froma a sub site):

From the root site:

Avatar of Russellbrown


Dear Rainer,

Thank you for the links.

I will try to break it down into the following steps and would appreciate your guidance.

1. create a procedure in Excel VBA module i.e. ImportListGUIDs()
2. insert the appropriate codes from the references/links
3. check Tools > References, if any
Step 1
Sub ImportListGUIDs()
GrabGUID "S:\Lists\"
End sub

Function GrabGUID (ByVal sList )
'------the lists in question sit in the "Lists" folder of Sharepoint
'-----loop through the lists and print out the respective GUIDs, or its equivalent

End Function
Step 2
Code sample: not applicable, as it relates to Sharepoint site. Do I ignore it?
Calling Webservice from within Excel: you have the equivalent in Excel 2012 VBA? The assignment does not include Visual Studio/.NET capability.-----------------------------------------------------------
Would appreciate your help to kick start Steps 1 & 2. Thank you.

it is recommended to use .NET functionality in Excel 2010 but you may also use VBA.

1. Open VBA project
2. Add reference to Microsoft Xml 6
3. Add this code to get the response - just modify the url to your site

Sub DoIt2()
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhtp As New MSXML2.XMLHTTP
    Dim xmlDoc As New DOMDocument
    sURL = ""
    sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
    sEnv = sEnv & "<soap:Envelope xmlns:xsi="""" xmlns:xsd="""" xmlns:soap="""">"
    sEnv = sEnv & "  <soap:Body>"
    sEnv = sEnv & "    <GetListCollectionResponse xmlns="""">"
    sEnv = sEnv & "      <GetListCollectionResult><xsd:schema>schema</xsd:schema>xml</GetListCollectionResult>"
    sEnv = sEnv & "    </GetListCollectionResponse>"
    sEnv = sEnv & "  </soap:Body>"
    sEnv = sEnv & "</soap:Envelope>"
    With xmlhtp
        .Open "post", sURL, False
        .setRequestHeader "Host", ""
        .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
        .setRequestHeader "soapAction", ""
        .send sEnv
        xmlDoc.LoadXML .responseText
        MsgBox .responseText
    End With
End Sub

Open in new window

Hi Rainer,

Thank you for the instructions.
1. When I change the URL to root:
 sURL = ""
I get a string of details.

I only wanted the GUIDs of all the lists residing in the  "Lists" folder of Sharepoint, so I change the URL ( see #2 ).

2. sURL = ""
I am able to run the code, but the  MsgBox .responseText returns 404 NOT FOUND

Did I code the URL correctly? or Did I miss out something else?

Appreciate your troublshooting. Thanks.
Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Dear Rainer,

The code, save for a couple of minor variable declarations using DIM, works beautifully. This is exactly what I am looking for. Many thanks for your steady guidance and prompt response. It has been a pleasure correpsonding with you and looking forward to more support from you.
Very well done. Thank you very much.
Hi Rainer,

I ran into a bug with the code. Can I raise the issue here for continuity sake? Thank you.