How to Export a SharePoint User List to Excel

There may come a time when you may need to export your user list to excel.  It’s not that difficult.

  1. Copy the following path into notepad/word pad: http://yoursite.com/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=[LISTID]&View=[VIEWID]&CacheControl
  2. Minimize the text editor and navigate to Site Actions à Site Setting à People and Groups à All People.
  3. Click Settings on the option bar and click List Settings
  4. Click on “List View” under views.   This will take you to the “Edit View” screen
  5. Copy the url of the list view.  It will look similar to the following:http://yoursite.com/_layouts/ViewEdit.aspx?List=%7B02ACF6F6%2D8343%2D4244%2D81B8%2D68BF95F9C6D0%7D&View=%7B56BB9BDD%2DABB0%2D4CB6%2DA946%2D59A85A8EC2F9%7D&Source=%252F%255Flayouts%252Flistedit%252Easpx%253FList%253D02acf6f6%252D8343%2
    52D4244%252D81b8%252D68bf95f9c6d0%2526Source%253Dhttp%25253A%25252F%25252F
    spdev%25252Eprsontrack%25252Ecom%25252F%25255Flayouts%25252Fpeople%25252Easpx
    %25253FMembershipGroupId%25253D0%252526FilterField1%25253DContentType%252526
    FilterValue1%25253DPerson%252526View%25253D%25257B56BB9BDD%25252DABB0%2525
    2D4CB6%25252DA946%25252D59A85A8EC2F9%25257D%252526Filter%25253D1
  6. Copy the “List” and “View” IDs for the url that was created in step 5.  You list id will follow “List=”.  The same applies for the view id.  Here is the example from above: List=%7B02ACF6F6%2D8343%2D4244%2D81B8%2D68BF95F9C6D0%7D&View=%7B56BB9BDD%2DABB0%2D4CB6%2DA946%2D59A85A8EC2F9%7D
  7. From this example, copy %7B02ACF6F6%2D8343%2D4244%2D81B8%2D68BF95F9C6D0%7D and replace [LISTID] from step one.  This includes replacing the LISTID and the brackets.  Also, do not include the & symbol.  Follow the same steps for the “View”.
  8. Once you have finished, your completed url should look like this:http://yoursite.com/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=%7B02ACF6F6%2D8343%2D4244%2D81B8%2D68BF95F9C6D0%7D&View=%7B56BB9BDD%2DABB0%2D4CB6%2DA946%2D59A85A8EC2F9%7D&CacheControl
  9. Open a browser, and copy and paste the completed url that you completed in step 8.
  10. A window will pop up asking you to open or save the file.  Click open and the file will open in excel.
  11. A security warning will also pop up.  Click enable.
    open_excel

 

 

 

 

 

12.  After you are finished making your changes, you can save this like any other Excel file.

 

 

 

Comments

Leave a Reply

%d bloggers like this: