In this article, we are going to discuss about a SharePoint tip which is not present in the User Groups by default.
Read this article to know How to Programmatically get the users with the Group Name in SharePoint
Scenario:
From any list in the SharePoint, you can export the whole list in to a excel file or you can edit and save the excel file.
But is that possible in SharePoint User Groups?
Yes, it is possible.
Solution:
Solution:
1. Go to Site Settings (http://sitname/_layouts/settings.aspx) -> Under Users and Permissions Select People and groups
2. You will be seeing a URL like this http://sitname/_layouts/people.aspx?MembershipGroupId=5
3. Then navigate to the List Settings in the people and groups of a particular group which you want to export to the excel file
4. Now, in the URL you will see like this:
http://sitname/_layouts/listedit.aspx?List=72bcbfee-c10e-41ba-b128-2769c8fe9cc9&Source=%2Fpeople%2Easpx%3FMembershipGroupId%3D5
5. If you want you retrieve the users list from any view, then click on the view from the views list as shown below:
Scroll to the bottom of the page and click on the view you want to export
6.Now, copy the URL from the address bar and keep it in a notepad which is something like below:
http:// sitname /_layouts/ViewEdit.aspx?List=%7B72BCBFEE%2DC10E%2D41BA%2DB128%2D2769C8FE9CC9%7D&View=%7B4A52DE00%2D9B7C%2D4FDC%2DAC39%2D960FDF18F1F3%7D&Source=%252F%255Flayouts%252Flistedit%252Easpx%253FList%253D72bcbfee%252Dc10e%252D41ba%252Db128%252D2769c8fe9cc9%2526Source%255Flayouts%25252Fpeople%25252Easpx%25253FMembershipGroupId%25253D5
5. If you want you retrieve the users list from any view, then click on the view from the views list as shown below:
Scroll to the bottom of the page and click on the view you want to export
http:// sitname /_layouts/ViewEdit.aspx?List=%7B72BCBFEE%2DC10E%2D41BA%2DB128%2D2769C8FE9CC9%7D&View=%7B4A52DE00%2D9B7C%2D4FDC%2DAC39%2D960FDF18F1F3%7D&Source=%252F%255Flayouts%252Flistedit%252Easpx%253FList%253D72bcbfee%252Dc10e%252D41ba%252Db128%252D2769c8fe9cc9%2526Source%255Flayouts%25252Fpeople%25252Easpx%25253FMembershipGroupId%25253D5
From the above URL, we have to copy the LIST ID and VIEW ID (marked bold in the above URL)
7. Now, we can easily export the people and groups in to excel using the following dll from the SharePoint server.
http://[sitename]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=[LISTID]&View=[VIEWID]&CacheControl=1
8. Now, replace the site name, LIST ID and VIEWID in the above URL:
I will get the final URL as:
http://sitename/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=%7B64DAB4AC%2DD043%2D4DA4%2D85C7%2D9D4521C3F4CC%7D&View=%7B0FAE8D90%2D59D5%2D48F4%2DA54E%2DF02333777C7F%7D&CacheControl=1
Note:
To know more about the owssvr.dll please read this article
Open the above URL in the address bar, you will get an option to save an EXCEL FILE.
9. Open the Excel file, you will be asked for authentication, enable the external content and open it.
10. That’s it. You will see all the users in the groups with their details exported to the excel file.
11. This excel file exported using this method has all the users from the user groups but it is not filtered by user group name.
Read this article to achieve exporting the SharePoint user groups with the group name Exporting SharePoint User Groups with Group Names.11. This excel file exported using this method has all the users from the user groups but it is not filtered by user group name.
Read this article to know How to Programmatically get the users with the Group Name in SharePoint
Rather than a List ID, I get a MembershipGroupID. If I insert this ID in place of [LISTID], the URL does not work. If I change List=[LISTID] with MembershipGroupID=[the group ID], then I am able to download the iqy file, but when opening it, I receive an error saying:
ReplyDeleteThe list does not exist. It may have been deleted by another user."
Any ideas?
Hi, This will work as expected and I have updated the article with more screen shots about getting the LIST ID and VIEW ID from the URL. Hope this helps you! Let me know if you have any queries.
DeleteThis technique worked as described, but it only provides a dump of all users registered for the site. Is there a way to filter for a specific group ID?
ReplyDeleteHi Nicainniva,
DeleteRead this article, to know how to retrieve the Users with group names http://sharepoint-works.blogspot.com/2012/05/programmatically-get-sharepoint-users.html#.T-C3axdo3-s
Hope this helps you!
Hi
ReplyDelete@ Anonymous and Nicainnva
Here, I would like to explain about the use of owssvr.dll.
1. This dll is a part of Microsoft Office server Extensions (OSE).This file gets accessed when someone visiting your site has Microsoft Office and Internet Explorer installed and has enabled the "Discuss" toolbar in his/her browser. When the toolbar is enabled, the browser will automatically query the file when visiting your site to determine if the OSE extensions are installed.
2. This dll works as an ISAPI extensions (Frontpage extensions) that implements the SharePoint Team Services functionality, such as list creation, deletion and authoring as well as the HTML page rendering system.
3. It creates a virtual table that stores data and metadata and forms an abstraction on top of the database. With this, it allows the user to manage tabular data in a Web site environment.
4. This is the reason, we use this dll directly from the browser. Here, we are remotely invoking the functions against sharepoiint.
You will find the OWSSVR.DLL in
1.SharePoint 2010 Server Physical Path:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI
2.WSS 3.0 and MOSS:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI
I am working on finding a solution which will export the users for a particular group. Soon I will post the same.
Also, I would like to share a link where there is a custom code for downloading one particular group into excel.
http://www.myrocode.com/post/2009/10/12/SharePoint-2007-Extract-SharePoint-Users-informations-to-an-Excel-file-format.aspx
Hope this helps you!
Hi
ReplyDelete@ Anonymous and Nicainnva
As a work around for exporting the user groups from the SharePoint site with the user groups, I have tried using SQL query.Read this article for more details.
http://sharepoint-works.blogspot.com/2012/04/exporting-sharepoint-user-groups-with.html
Great Post. Very useful!
ReplyDelete@ Anonymous
ReplyDeleteThanks!
Quick question:
ReplyDeleteLike the first commenter said, my url does not say [LIST ID] but instead says [MembershipGroupID]. I replaced the [LIST ID] with what was located there (a five digit number) but Sharepoint is telling me that the table does not exist or someone deleted it. Can you advise on this? Thanks again for the tutorial!
Hi, This will work as expected and I have updated the article with more screen shots about getting the LIST ID and VIEW ID from the URL. Hope this helps you! Let me know if you have any queries.
DeleteHi Maruthachalam,
ReplyDeleteThanks for great works !
Advise please how to do the folow. I wish export list of users which have non-inherited permissions to particular SP list or bibliothek. Can I do it? Example URL here :
http://portal/branch2/_layouts/user.aspx?obj=%7B8C4D2D7E%2D70CC%2D45D9%2D8343%2DEDE9C1110302%7D,doclib&List=%7B8C4D2D7E%2D70CC%2D45D9%2D8343%2DEDE9C1110302%7D
Hi
DeleteAfter seeing your URL, I could see you are on the page which you will see when you click on People and groups in SharePoint, hence click on any of the groups where you will get all the users in SharePoint site.
Also, if you like to get the users with group name read this article,
http://sharepoint-works.blogspot.com/2012/05/programmatically-get-sharepoint-users.html
Hope this helps you!
This does not work for Sharepoint 2010 Foundation does it? I do not see List Settings as an option when I look at Groups. Can't get it to work for me :(
ReplyDeleteThe List Settings option is a general one which is available in all versions of SharePoint. There is nothing to do with versions of SharePoint.
DeleteI also guess that you do not have enough permissions for the site you are accessing and hence the "List Settings" options is disabled or hidden from your view.
Hope this helps you!
I have the same problem. But being a site owner - which permission could I be lacking?
DeleteI found the response to my question myself :-)
DeleteThe "List Settings" menu seems bo be available only to "Site Collection Administrators", not to "Site Owners"!
Apart from that:
The simplest way to retrieve comprehensive user data from the "UserInfo" list is to simply create a table in MS Access linked to this list. But I don't know how to retrieve group membership information using MS Access either :-(
Does anybody know?
"List Settings" is available only in top level site and not on the subsite
DeleteHi, I'm new to SharePoint2010 so I'm probably doiong things I shouldn't be doing. I viewed the Group list of users and in frustration tried Ctrl A (select all), Ctrl C (copy) and pasted it into Notepad. I had to copy each page of users (I had 3 pages) and some extra, unnecessary text came with it, but I got a pretty clean list of users I could open in Excel. Hope this helps - Eddie C.
ReplyDeleteThis is the easiest way to export sharepoint users in excel. just navigate to People and Groups. Copy the URL, open your excel then navigate to Data tab. select "From Web", paste the address then go. click the yellow arrow beside the word "Group" then import. wait for the data to load then you are done.
ReplyDeleteThank you ymanksy! Your solution worked wonderfully!
DeleteThis gives me all the users on the Sharepoint site and not just the users in one specific group. Even if I navigate to that group and select that URL.
ReplyDelete