Export site users to excel

SharePoint comes with a great feature which is that its lists and libraries  can be exported to excel, (if you then change the settings in the data connection within excel to refresh on file open you can then save this excel and every time you open it, it will be up to date with the list, saving you from keep navigating to the list and pressing exporting to excel)

unfortunately there is no export to excel option on the site people and groups list, so to create this feature you have to create a query file (this is the file that is created when in a regular list you click export to excel)

To create a query file first create a new .txt file then paste the following code into it changing the text in bold to your details;

WEB 1 http://SITE COLLECTION URL/_vti_bin/owssvr.dll?XMLDATA=1&List={LIST ID}&View={LIST VIEW ID}&RowLimit=0&RootFolder=%2fLists%2fLIST NAME

Selection={LIST ID}-{LIST VIEW ID} EditWebPage= Formatting=None PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False DisableDateRecognition=False DisableRedirections=False SharePointApplication=http://SITE COLLECTION URL/_vti_bin SharePointListView={LIST ID} SharePointListName={LIST VIEW ID} RootFolder=/Lists/LIST NAME

Save the file, then rename the file to  .IQY

Double click and open with Excel and it will export your site collection people and groups list into the excel workbook.

Here is an example of the code;

WEB 1 https://www.sitecollection.com/_vti_bin/owssvr.dll?XMLDATA=1&List={37fd0da1-ce48-405b-b8ec-d8b364dcaddb}&View={F86875B9-C5A8-4AAE-94E4-9E7D1BB9551F}&RowLimit=0&RootFolder=%2fLists%2fList1

Selection={37fd0da1-ce48-405b-b8ec-d8b364dcaddb}-{F86875B9-C5A8-4AAE-94E4-9E7D1BB9551F} EditWebPage= Formatting=None PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False DisableDateRecognition=False DisableRedirections=False SharePointApplication=https://www.sharepointcollection.com/_vti_bin SharePointListView={F86875B9-C5A8-4AAE-94E4-9E7D1BB9551F} SharePointListName={37fd0da1-ce48-405b-b8ec-d8b364dcaddb} RootFolder=/Lists/List1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s