How to export a SharePoint list to Excel 2007 and enable synchronization that writes back to the SharePoint site

When you export a Windows SharePoint Services (WSS) 3.0 or Microsoft Office SharePoint Server (MOSS) list to Microsoft Office Excel 2007 the SharePoint list is converted to a table. It is not possible to maintain a read/write link with the SharePoint list.

Note: Existing Microsoft Office Excel 2003 workbooks can still be synchronized in a SharePoint linked list.

Begin by using the Export to Spreadsheet function on your SharePoint List. Open the .igy file, and enable the content. You will be looking for some XML contained in the Connection Properties under Command text. The XML will look something like this. Make sure you use the <VIEWGUID></VIEWGUID>.

<LIST>
<VIEWGUID>{EE899B13-9557-4559-B276-F16E769467AC}</VIEWGUID>
<LISTNAME>{F39B72F0-6A75-4F53-9B3B-91A127FF641C}</LISTNAME>
<LISTWEB>http://sharepoint/MySite/_vti_bin</LISTWEB>
<LISTSUBWEB></LISTSUBWEB>
<ROOTFOLDER>/MySite/Lists/MyList</ROOTFOLDER>
</LIST>

Next you will need to create a macro. Run the macro once, and a correctly configured list that allows two way refresh with your SharePoint list will be created. Make sure the name of your list correctly entered between the source and the guid.

Sub LinkMyList()

     ActiveSheet.ListObjects.Add _
        SourceType:=xlSrcExternal, _
        Source:=Array("
http://sharepoint/MySite/_vti_bin", _
        "MyList", _
        "{EE899B13-9557-4559-B276-F16E769467AC}"), _
        LinkSource:=True, _
        Destination:=Range("A1")

End Sub

Reference: A SharePoint list is converted to a table, and synchronization of the SharePoint list is disabled after you save an Excel 2007 workbook

This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to How to export a SharePoint list to Excel 2007 and enable synchronization that writes back to the SharePoint site

  1. The code worked with me, the problem is that im trying to do a mass update, with a copy-past in more than 30 rows, but when i do this, the ID of the list get lost, and create new IDs (rows) to the list.
    Anybody is getting this problem to? How do you work with mass update in SharePoint Lists?

    • In Fact, this problem occurred only in the ID 20. If i try to change any information in any other row, works fine now. I’ve already deleted ID 20, but sharepoint assume another line to the ID, and the problem stills

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