Programming Excel with VBA and .NET

Look Up a List GUID

The ListObjects Add method uses a GUID when inserting an existing SharePoint list into a worksheet. You can find this GUID manually by looking on the SharePoint site, or you can use the GetListCollection method to look up the GUID by name as shown here:

Function GetListGUID(listName As String) As String
    ' Requires web reference to SharePoint Lists.asmx
    Dim lws As New clsws_Lists
    Dim xn As IXMLDOMNodeList  ' Requires reference to Microsoft XML
    Dim root As IXMLDOMElement
    Dim ele As IXMLDOMElement
    Set xn = lws.wsm_GetListCollection
    Set root = xn.Item(0)
    For Each ele In root.childNodes
        If LCase(ele.getAttribute("Title")) = LCase(listName) Then
            GetListGUID = ele.getAttribute("Name")
            Exit Function
        End If
    Next
    GetListGUID = ""  ' Return empty string if not found.
End Function

Looking at the preceding code, it may occur to you that you need to know a lot about the structure of the XML that the Lists Web Service uses before you can accomplish much. It’s easy to view an IXMLDOMElement during debugging by printing it to the Immediate window as shown here:

Debug.Print root.xml

Unfortunately, what you get is a mass of text with no whitespace. To see the structure a little more clearly, you have to use XML reader and writer objects to format the output. The following helper function does just that:

Function PrettyPrint(xml As String) As String Dim rdr As New SAXXMLReader ' Requires reference to Microsoft XML Dim wrt As New MXXMLWriter ' Requires reference to Microsoft XML Set rdr.contentHandler ...