Uploading Spreadsheets into SharePoint Lists

Ran into an interesting issue when trying to upload a spreadsheet from Excel 2007 into SharePoint 2007.

To do so, you would normally click on Site Actions --> Create --> Import Spreadsheet.  In the past, I have found this to be the easiest way to create and populate a SharePoint List.  however, when doing so today, I got the following:

Import to Windows SharePoint Services list

Method 'Post' of object 'IOWSPostData' failed. ?!?!?!

image

Fortunately, I found the solution over on Travis' blog.

Importing lists from Excel 2007 returns a Method 'Post' of object 'IOWSPostData' failed dialog. Again, not really a problem with WSS 3.0 but rather the result of a failed Application.SharePointVersion() call in the Excel Add-In which results in Excel attempting to use the IOWSPostData.Post() method to publish the Excel range which is used with SharePoint Team Services 1.0. By forcing the version lookup result variable to 2 or greater, Excel will use SOAP to communicate with WSS 3.0 and the publish request will be successful. To make this change, open the Excel Add-In EXPTOOWS.XLA locate in C:\Program Files\Microsoft Office\Office12\1033 by default*. Press Alt+F11 to display the Visual Basic code editor and search (Ctrl+F) for the line lVer = Application.SharePointVersion(URL) . Comment out that line with a single quote and add the line lVer=2 so your Intialize() method should now look like this:

Sub Initialize(List, Title, URL, QuickLaunch)
    strQuickLaunch = QuickLaunch
    aTarget(iPublishURL) = URL
    aTarget(iPublishListName) = List
    aTarget(iPublishListDesc) = Title
    'lVer = Application.SharePointVersion(URL)
    lVer = 2
End Sub

If the Application.SharePointVersion(URL) method is successful then lVer for WSS 3.0 will equal 3. Save your changes and try importing your list in to WSS 3.0 again.

*Note: If you are using 64-bit Windows, the XLA file is actually under: C:\Program Files (x86)\Microsoft Office\Office12\1033\. If you are running Windows Vista, you will need to run Excel as administrator in order to save the modified file back.

image

After doing so, I am now able to import a spreadsheet into a SharePoint list with no problems.  Huzzah!

image

That having been said, I now see that there is an Excel Add-on that will specifically allow for two-way-synchronization between spreadsheets in Excel and Lists in SharePoint.  In fact, I blogged about it previously (maybe I should ready my own posts!).  After installing the add-in, there is a new "Export Table to SharePoint List" option in Excel:

image

The resulting table in SharePoint is actually an Access Web Datasheet as opposed to a SharePoint List.  It works great as a datasource (especially with the two-way synchronization), but is plug-ugly for customer-facing lists.

image 

Depending on your needs (whether you need a datasource or a customer-facing list), you may use either of the options above.  Enjoy!