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

When you save a Microsoft Excel workbook in a Microsoft Office Excel 2007 format to a Windows SharePoint Services 3.0 linked list, the SharePoint list is converted to a table. Additionally, synchronization of the SharePoint list is disabled.

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

This behavior is by design. This behavior occurs because Excel 2007 does not support synchronization of SharePoint lists that write back to the SharePoint site. Excel 2003 supports synchronization of SharePoint lists that write back to the SharePoint site.

To work around this behavior, run a Microsoft Visual Basic for Applications (VBA) macro to create a new SharePoint linked list that supports synchronization. To do this, follow these steps.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

1. On the SharePoint site, locate the linked list.
   
   Note Note the list name in SharePoint.
2. Export the linked list to an Excel 2007 table. To do this, follow these steps:
   1. Under Actions on the page that displays the list, click Export to Spreadsheet.
   2. Save the .iqy file to the desktop.
3. Open the .iqy file in Excel 2007.
4. If you are prompted to enable data connections on your computer, click Enable.
   
   Note Click Enable only if you believe that the connection to the data on the SharePoint site is safe to enable.
5. In the Import Data dialog box, click Table under Select how you want to view this data in your workbook, click New worksheet under Where do you want to put the data, and then click OK.
6. On the Data tab, click Connections in the Connections group.
7. In the Workbook Connections dialog box, click the connection to the SharePoint list, and then click Properties.
8. In the Connection Properties dialog box, click the Definition tab, and then copy the GUID and the URL to a text editor.
   
   Note You will use the GUID and the URL in step 12.
9. Click OK, and then click Close.
10. Press ALT+F11 to start the Visual Basic Editor.
11. On the Insert menu, click Module.
12. In the module sheet, create a VBA macro that resembles the following VBA macro. In this macro, "GUIDString" is the GUID and "URL String" is the URL that you copied to the text editor in step 8. Additionally, "ListNameString" is the name of the list on the SharePoint site.

    Sub LinkedSharePointList()
    
         ActiveSheet.ListObjects.Add SourceType:=xlSrcExternal, _
         Source:=Array(<"URL String">, <"ListNameString">, _
         <"{GUIDString}">), LinkSource:=True, Destination:=Range("A1")
    
    End Sub
    

    The following code sample is a correctly written macro for this example.

    Sub LinkedSharePointList()
    
         ActiveSheet.ListObjects.Add SourceType:=xlSrcExternal, _
         Source:=Array("http://myserver/_vti_bin", "MyList", _
         "{D607DDB3-710F-433C-B6E6-3878E041D669}"), LinkSource:=True, Destination:=Range("A1")
    
    End Sub
    

    Note You cannot use the HasHeaders argument for the ListObjects.Add method. If you do this, you will receive the following error message:
    Named argument not found.
13. Save the file in the Excel 97-2003 Workbook (*.xls) format. To do this, follow these steps:
    1. In the Visual Basic Editor, click Close and Return to Microsoft Excel on the File menu.
    2. Click the Microsoft Office Button, and then click Save As.
    3. In the Save as type box, click Excel 97-2003 Workbook (*.xls), and then save the file to the SharePoint list.
14. Delete the table that contains the list.
    
    Note You cannot link the list to SharePoint if you do not delete the table that you created from the .iqy file.
15. On the Developer tab, click Macros.
16. In the Macro dialog box, click LinkedSharePointList, and then click Run.
    
    Note when the list has been created, save and close the workbook.
17. To write changes to the list to SharePoint, follow these steps:
    1. In Excel 2007, open the .xls file that you saved in step 13.
    2. Modify the workbook.
    3. Right-click in the list.
    4. Click Table.
    5. Click Synchronize with SharePoint.
    6. Save the .xls file to keep the changes.
18. To pull changes that are made to the linked list in SharePoint by other users, follow these steps:
    1. Right-click in the list.
    2. Click Table.
    3. Click Synchronize with SharePoint.
    4. Save the .xls file to keep the changes.