Monday, March 12, 2012

Export SharePoint User Groups in Excel

In this article, we are going to discuss about a SharePoint tip which is not present in the User Groups by default.
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:
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

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.
Read this article to know How to Programmatically get the users with the Group Name in SharePoint 

Link WithIn

Related Posts Plugin for WordPress, Blogger...