How to use MS Access / VBA to get list of users within a Sharepoint group

I am creating an MS Access 2007 application integrated with a SharePoint 2010 list as the data source.

I need to inspect the users whom are members of a particular 开发者_JS百科Sharepoint user group to support some functionality I'm building into the application. Is there a way to determine the relationship of sharepoint users to groups from within VBA code? Thanks.

---

I use a CAML query via SOAP, then feed the response into an XML parser

may need some fine tuning, but this will get you most of the way there. I use something similar.

It might look complex, but all being well you will just have to change the URL to your community.

enable 'Debug.Print .responseText and 'debug.print .status to debug any problems. A status of 200 means it has found the site ok.

function start_here()

set user_list = get_users("http://sites.company.com/sites/00672")

for each n in user_list
    debug.print str(n), userlist(str(n))
next

end function

Function get_users(site_URL)

Dim xmlDoc
Set xmlDoc = CreateObject("Msxml2.DOMDocument")

request = "<?xml version='1.0' encoding='utf-8'?>" + _
        "<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/'>" + _
            "<soap:Body>" + _
                "<GetUserCollectionFromSite xmlns='http://schemas.microsoft.com/sharepoint/soap/directory/' />" + _
            "</soap:Body>" + _
        "</soap:Envelope>"

    With CreateObject("Microsoft.XMLHTTP")
      .Open "Get", site_URL & "/_vti_bin/usergroup.asmx", False, Null, Null
      .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
      .setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/directory/GetUserCollectionFromSite"
      .send request
      'Debug.Print .status
      'Debug.Print .responseText
       xmlDoc.LoadXML (.responseText)
     End With

Set nodesCollection = xmlDoc.SelectNodes("//Users/User")
Set ID = xmlDoc.createNode(1, "xml", "")
Set user_name = xmlDoc.createNode(1, "xml", "")

Set user_col = New Collection

    For Each nodeElement In nodesCollection
        Set ID = nodeElement.Attributes.getNamedItem("ID")
        Set user_name = nodeElement.Attributes.getNamedItem("Name")
        user_col.Add user_name.Text), Str(ID.Text)
    Next

Set get_users = user_col

end function

---

I think there is a small typo in the function....

Instead of: user_col.Add user_name.Text), Str(ID.Text)

Should be - without the extra ) : user_col.Add user_name.Text, Str(ID.Text)