There may come a time when you may need to export your user list to excel. It’s not that difficult.
- 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
- Minimize the text editor and navigate to Site Actions à Site Setting à People and Groups à All People.
- Click Settings on the option bar and click List Settings
- Click on “List View” under views. This will take you to the “Edit View” screen
- 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 - 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
- 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”.
- 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
- Open a browser, and copy and paste the completed url that you completed in step 8.
- A window will pop up asking you to open or save the file. Click open and the file will open in excel.
- A security warning will also pop up. Click enable.
12. After you are finished making your changes, you can save this like any other Excel file.
I just tried this and get the following error….what am I doing wrong? Have you seen this before? Thanks for your help.
Internet explorer cannot download owssvr.dll from sitename
Internet explorer was not able to open this internet site. the requested site is either unavailable or cannot be found. Please try again later.
Has your SharePoint site been added to your trusted sites in Internet Explorer?
Hi
Is it possible to export the users of a particular group with the GroupMembership ID ?
Any Ideas ?
Thanks in Advance.
Brilliant tip! Saved me masses of time. Thanks.
I try it its very good but i want that when i export Auto open exel and the window to print the table who i do it?
thanks.
it’s work.. great article.., now i can get all email address from all user on AD and sharepoint
Too good resolution. it works ! TAT
Great solution, works like a charm
Nicely done, thanks!
hi, great tips. but actually i was looking for a solution how to export a sharepoint list to excel including email addresses (site column is person/group). At the moment the email adresses show up as ‘lastname, firstname’. could you help me?
Thanks a lot it worked… searched everywhere but your is really structured.