Tip #44. How to export user information to Excel using "Export to spreadsheet"

Rate this item
(2 votes)

SharePoint provides you "Export to Spreadsheet" feature across almost all list, allowing you to export your list content to Excel. But not all lists have such functionality. For example, this option is not available for User list.

It is either hidden from the toolbar menu in "People and Groups" http://[site]/_layouts/people.aspx (left picture), or User Info view has no toolbar http://[site]/_catalogs/users/simple.aspx (right picture)

people

But there is a trick, how to export list of users to Excel. This export functionality is just using owssvr.dll, so you can use the following URL to open your users in Excel

http://[site]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=[LISTID]&View=[VIEWID]&CacheControl=1

Everything you need is get the right ID of the list and its view. To get these values follow the next steps:

  1. Go to the "People and Groups" page of your site
  2. Navigate  to "Settings->List Settings" options via Toolbar
  3. Click on "List View" in "View" section to edit the List View.
  4. Choose the fields you want to export (need to click OK and open view for edit again)
  5. Copy the "List" and "View" IDs from URL in this the page, where you edit List View.
  6. Save copy-pasted ID and replace [LISTID] and [VIEWID] in our URL.
  7. Open original URL http://[site]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy in browser, with the right values (IE asks to open owssvr.dll, and Excel asks to activate macros)
After these steps you will have the Excel with you user list.

PS: Take into account that "User Information List" is not synchronized with profiles, and stored individually per site collection. This list is populated when you grant permissions (more about UIL there http://sharepointsherpa.com/2008/01/31/employee-directory-using-user-information-list-in-sharepoint-2007/)

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.