Hi, Is there any weay to export the contents (users) of a SharePoint group into excel for reporting purposes? I have followed the instructions here - http://vspug.com/tonstegeman/2007/10/03/export-user-information-to-excel-using-quot-export-to-spreadsheet-quot-in-sharepoint-2007/ - and created excel reports based on columns in the User Informatiom list but there does not appear to be a column for Group. Any help would be greatly appreciated.

Have you physical access to the server or can you only access it via HTTP?

Have you physical access to the server or can you only access it via HTTP?

Hemendra, I like the sound of your idea but would have no idea how to put it into practice!

I do have physical access.

Hemendra, I like the sound of your idea but would have no idea how to put it into practice!

I do have physical access.

Here is a sample VBA macro that is able to retreive all groups (comma separated of a user by his login name) via HTTP soap sharepoint web services. Function GetUserGroups(userName As String,webUrl As String) Dim soapClient As MSXML2.XMLHTTP Set soapClient = New MSXML2.XMLHTTP Dim xmlDoc As MSXML2.DOMDocument Set xmlDoc = New MSXML2.DOMDocument 'Creating SOAP Envelope Dim body As String body = "<?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>" & _ "<GetGroupCollectionFromUser xmlns=""http://schemas.microsoft.com/sharepoint/soap/directory/"">" & _ "<userLoginName>" & userName & "</userLoginName>" & _ "</GetGroupCollectionFromUser >" & _ "</soap:Body>" & _ "</soap:Envelope>" 'Load it in an xml document xmlDoc.LoadXML body 'Open HTTP port soapClient.Open "POST", webUrl & "_vti_bin//usergroup.asmx", False 'Send soap envelope via HTTP soapClient.send xmlDoc 'Parse result to retreive groups Dim strGroups As String strGroups = "" If soapClient.readyState = 4 Then If soapClient.Status = 200 Then Dim grp As IXMLDOMNode For Each grp In soapClient.responseXML.getElementsByTagName("Group") Debug.Print (grp.XML) Dim attr As IXMLDOMAttribute For Each attr In grp.Attributes If attr.Name = "Name" Then If strGroups <> "" Then strGroups = strGroups & ";" End If strGroups = strGroups & attr.Value End If Next Next Else Debug.Print XMLHttpReq.Status & ", " & XMLHttpReq.responseText End If End If GetUserGroups = strGroups End Function

You could also call the GetAllUserCollectionFromWeb using this envelope <?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> <GetAllUserCollectionFromWeb xmlns="http://schemas.microsoft.com/sharepoint/soap/directory/" /> </soap:Body> </soap:Envelope> Just ask if you have any problem on implementing this technique...

You could also call the GetAllUserCollectionFromWeb using this envelope <?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> <GetAllUserCollectionFromWeb xmlns="http://schemas.microsoft.com/sharepoint/soap/directory/" /> </soap:Body> </soap:Envelope> Just ask if you have any problem on implementing this technique...

SharePoint OOB has option to export users to excel. Why not use the functionality ? Here are the steps. 1. Obtain "listID" & "ViewID" for the list "User Information List". Here are the steps to obtain these ids. a. Go to “People and Groups” page of the site and navigate to “List Settings” (“Settings->List Settings”) via Toolbar. b. Choose the list from the list of views in the bottom portion of the page. c. In the address bar of the page, you would find the listid and viewed. 2. Replace the viewID and ListID we obtained from previous step in the url http://[sitename]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=[LISTID]&View=[VIEWID]&CacheControl=1 Save the qry and open the same in excel. You may be asked to authenticate yourself before opening the excel. Once you get all the details, you can filter the content to obtain appropriate results

SharePoint OOB has option to export users to excel. Why not use the functionality ? Here are the steps. 1. Obtain "listID" & "ViewID" for the list "User Information List". Here are the steps to obtain these ids. a. Go to “People and Groups” page of the site and navigate to “List Settings” (“Settings->List Settings”) via Toolbar. b. Choose the list from the list of views in the bottom portion of the page. c. In the address bar of the page, you would find the listid and viewed. 2. Replace the viewID and ListID we obtained from previous step in the url http://[sitename]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=[LISTID]&View=[VIEWID]&CacheControl=1 Save the qry and open the same in excel. You may be asked to authenticate yourself before opening the excel. Once you get all the details, you can filter the content to obtain appropriate results

Maybe you could re-read the first post and notice that this was exactly what he was doing at first... the problem is that he also need to get sharepoint group for each user return by the oob query....

Maybe you could re-read the first post and notice that this was exactly what he was doing at first... the problem is that he also need to get sharepoint group for each user return by the oob query....

I wrote an app that will export the users and groups. Uses web services. Might help. SharePoint User Auditor http://www.thesug.org/blogs/lsuslinky/sua Tim

I wrote an app that will export the users and groups. Uses web services. Might help. SharePoint User Auditor http://www.thesug.org/blogs/lsuslinky/sua Tim

This query is what I have been trying to use but it's not quite returning the results I need. I'm actually after only the members of the Site Members sharepoint group as opposed to All People. My problem is that I don't think I can find the correct List. On the browser it's returning https://[sitename]/_layouts/people.aspx?MembershipGroupId=10 but when I look at the List Settings url the list is not limited to users who are members of the GroupID 10. Any ideas on how to adjust the query string to produce only members of this group?

This query is what I have been trying to use but it's not quite returning the results I need. I'm actually after only the members of the Site Members sharepoint group as opposed to All People. My problem is that I don't think I can find the correct List. On the browser it's returning https://[sitename]/_layouts/people.aspx?MembershipGroupId=10 but when I look at the List Settings url the list is not limited to users who are members of the GroupID 10. Any ideas on how to adjust the query string to produce only members of this group?

SharePoint OOB has option to export users to excel. Why not use the functionality ? Here are the steps. 1. Obtain "listID" & "ViewID" for the list "User Information List". Here are the steps to obtain these ids. a. Go to People and Groups page of the site and navigate to List Settings (Settings->List Settings) via Toolbar. b. Choose the list from the list of views in the bottom portion of the page. c. In the address bar of the page, you would find the listid and viewed. On my People and Groups pages, there is not "Settings->List Settings" like there is for other SharePoint lists. There is a "Settings->Group Settings", but there are no views in this, so I can't figure out how to get the listid and viewid for the Group in question that I'd like to export to Excel. Am I missing something?

SharePoint OOB has option to export users to excel. Why not use the functionality ? Here are the steps. 1. Obtain "listID" & "ViewID" for the list "User Information List". Here are the steps to obtain these ids. a. Go to People and Groups page of the site and navigate to List Settings (Settings->List Settings) via Toolbar. b. Choose the list from the list of views in the bottom portion of the page. c. In the address bar of the page, you would find the listid and viewed. On my People and Groups pages, there is not "Settings->List Settings" like there is for other SharePoint lists. There is a "Settings->Group Settings", but there are no views in this, so I can't figure out how to get the listid and viewid for the Group in question that I'd like to export to Excel. Am I missing something?

From Excel you can import the group members In sharepoint, navigate to the group you want to export.Copy the URL from the browser.In Excel Toolbar Goto Data > From Web and copy the URL in address and click Go. Output will not be pretty but useful...I tried this with Excel 2010 and Sharepoint 2010 and was able to extract the membership details.