Exporting SharePoint User Information List to Excel

Duane OdumAs there is no User Profile Service in SharePoint Foundation 2010 or SharePoint Foundation 2013 one of the tasks that we are often asked to perform is to create a company directory in
SharePoint pulling from a client’s Active Directory.  In most cases we use SharePoint’s User Information list (More Information…).

Recently we had a client request an export of the User Information list to Excel which seems like an easy enough request but as the User Information list is a “special” list that is not really meant to
be treated like another list there is not an option to Export to Excel. Along comes SharePoint web services to the rescue.

By using Mike Berryman’s post above we are able get the ListID (GUID) of the User Information list and while on the list view we can simply View Source and find the ViewID (the ViewID will always be after the second entry of the ListID on the View Source) and will look something like this u002743818797-508e-4800-945f-abb89c9cbf7b.  Note: Please remove the u0027 for this to work properly.

At this point we simply need to input the Site, ListID, and ViewID into the appropriate places in the link: http://[sitename]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=[LISTID]&View=[VIEWID]&CacheControl=1 and you will be able to export your user information list as an iqy file from SharePoint just like any other list.


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