By: Rob Fisch | Updated: 2010-04-20 | Comments | Related: > SharePoint
Problem
With WSS2, I used to be able to read and write to SharePoint lists from MS Excel 2003. With WSS3 and MOSS 2007, that functionality seems to have been disabled. Does that functionality still exist?
Solution
Yes, in WSS3 and MOSS 2007, that functionality from MS Excel (any version) was removed. However, that functionality was reassigned to MS Access (2007), which is now a very powerful tool for SharePoint data management.
To 'link' to a SharePoint list, start a new MS Access database. Then click the "External Data" tab and select "SharePoint List" from "Import" section (not the Export section).
Enter the URL of the site where the list is that you want to connect to and select the "Link to the data source by creating a linked table" option. Then click "Next".
Check the list(s) and/or libraries you would like to link to. Then click "OK".
Reviewing the Access Objects
When the link is created, you will see your list(s) and an unexpected object called "User Information List". This is needed, because SharePoint stores user information in a special table. The list content only stores the "ID" of the user. The connection wizard automatically takes a copy of the needed users, and makes an automatic lookup reference to the the user information.
Below shows both the SharePoint list (top) and the new linked table in MS Access (bottom). The data matches perfectly.
When I change the data in MS Access (below), the SharePoint data gets immediately updated.
This is a very powerful resource for a SharePoint administrator. Think of the possibilities!
Next Steps
- Look for future tips on using MS Access to migrate SharePoint content between instances and even versions.
- Check out MSSQLTips.com for great information about Microsoft SQL Server.
About the author
Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-04-20
