Export SharePoint User Groups to Excel
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.
May 17th, 2010 2:14pm
Have you physical access to the server or can you only access it via HTTP?
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2010 12:45am
Have you physical access to the server or can you only access it via HTTP?
May 18th, 2010 12:45am
Hemendra,
I like the sound of your idea but would have no idea how to put it into practice!
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2010 10:33am
I do have physical access.
May 18th, 2010 10:33am
Hemendra,
I like the sound of your idea but would have no idea how to put it into practice!
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2010 10:33am
I do have physical access.
May 18th, 2010 10:33am
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
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2010 2:50pm
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...
May 18th, 2010 2:53pm
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...
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2010 2:53pm
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
August 2nd, 2010 1:55pm
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
Free Windows Admin Tool Kit Click here and download it now
August 2nd, 2010 1:55pm
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....
August 2nd, 2010 10:54pm
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....
Free Windows Admin Tool Kit Click here and download it now
August 2nd, 2010 10:54pm
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
August 3rd, 2010 3:00pm
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
Free Windows Admin Tool Kit Click here and download it now
August 3rd, 2010 3:00pm
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?
August 13th, 2010 9:37am
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?
Free Windows Admin Tool Kit Click here and download it now
August 13th, 2010 9:37am
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?
June 11th, 2012 8:38am
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?
Free Windows Admin Tool Kit Click here and download it now
June 11th, 2012 8:43am
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.
August 13th, 2012 6:17am