How to export user information to Excel

Posted: March 30, 2012 in Excel, Permission

SharePoint provides you “Export to Spreadsheet” feature across almost all lists, 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 information list.

Here are the details:

We can achieve this by using two methods:

1. OWSSVR Functionality

2. Open with access

1.OWSSVR Functionality:

How to export user information list 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

 

2. Open with access method: this is the most simple method to extract the data.

 1. Open any custom list by using the option “open with access”

2. When the list will open in access then you will see the option of “user information list” in the left hand side.

3. Copy and paste it in excel

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s