Retrieve the GUIDs for lists from the Sharepoint

Hi,

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:
http://msdn.microsoft.com/en-us/library/websvclists.lists.getlistcollection

Calling Webservice from within Excel:
http://msdn.microsoft.com/en-us/library/dd819156%28v=office.12%29.aspx

Just set the path to the list service to (if you want to retrieve lists froma a sub site):
http://yoursharepoint.com/sites/yoursite/_vti_bin/lists.asmx

From the root site:
http://yoursharepoint.com/_vti_bin/lists.asmx

HTH
Rainer

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
http://msdn.microsoft.com/en-us/library/websvclists.lists.getlistcollection > appears not applicable, as it relates to Sharepoint site. Do I ignore it?
http://msdn.microsoft.com/en-us/library/dd819156%28v=office.12%29.aspx >  would 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.

Hi,

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 = "https://workspace.jrbiz.de/content/eedemo/_vti_bin/lists.asmx"
    sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
    sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
    sEnv = sEnv & "  <soap:Body>"
    sEnv = sEnv & "    <GetListCollectionResponse xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">"
    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", "webservices.gama-system.com"
        .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
        .setRequestHeader "soapAction", "http://schemas.microsoft.com/sharepoint/soap/GetListCollection"
        .send sEnv
        xmlDoc.LoadXML .responseText
        MsgBox .responseText
    End With
End Sub

Select allOpen in new window

HTH
Rainer

Hi Rainer,

Thank you for the instructions.
1. When I change the URL to root:
 sURL = "http://597.mssharepointonline.com/_vti_bin/lists.asmx"
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 = "http://597.mssharepointonline.com/Lists/_vti_bin/lists.asmx"
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.

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.