Wednesday, April 21, 2010

Method 'Post' of object 'IOWSPostData' Failed in Sharepoint 2010

Over the past several months I've been testing out new features in Sharepoint 2010, one of which is the new chart webpart. To test it I've been trying to import a spreadsheet into Sharepoint 2010, but I've never gotten the import to work without an error, until now. Everytime I tried to import a spreadsheet, no matter how small or simple, I got a method 'Post' of object 'IOWSPostData' Failed error message. Here's what happened to me, and how I fixed the problem.

Background
I'm running Sharepoint 2010 (the release candidate) and Office 2007. I confirmed this error message does not occur if you're running Sharepoint 2010 and Office 2010, only when the environments are
mixed.
  1. From your site, open All Site Content. Click Create and select Import Spreadsheet. Give the list a name and navigate to the file location.

  2. Choose Range of Cells and select the range on the spreadsheet.



  3. Click Import. The following error message will appear:



  4. The same error will appear if you select a Named Range or a Table Range.
How to Fix It
I found several other folks (here and here are the best examples) had had this problem as well, but none of their fixes had worked for me because I had a hybrid environment of 2010 and 2007. I'm guessing that other folks will encounter this problem too, because big companies don't often migrate Office all at once, especially while they're evaluating new products like Sharepoint 2010.

All of the posts I'd read about this problem suggested editing the Excel Add-in file EXPTOOWS.XLA located in the \Program Files\Microsoft Office\Office 12\1033 folder. But editing that file didn't help me a bit. Turned out there's another version of that same file in \Program Files\Microsoft Office\Office14\1033, and that was the one that needed editing in my case.

Before you begin, a few notes for the uninitiated:
  • EXPTOOWS.XLA is a hidden file. In order to find it, you'll have to show all hidden files on the system. Do this through the Control Panel, Appearance and Personalization, Folder Options, Show Hidden Files and Folders.
  • The 1033 folder is read-only, so you 1) must be an administrator to write to the folder and 2) can't just edit the file and save it back to the same directory.
To modify the file:
  1. Once you've found the file, rename it to OldEXPTOOWS.XLA (this will give you a backup in case something happens). You may be prompted to confirm the change because this is a read-only folder.
  2. Double-click on OldEXPTOOWS.XLA. This will open Excel but it will look like nothing else is happening. On your keyboard, press ALT-F11 to open the built-in Visual Basic editor. It will look something like this:



  3. Open the code window and scroll until you find this section:

    Sub Initialize(List, Title, URL, QuickLaunch)
    strQuickLaunch = QuickLaunch
    aTarget(iPublishURL) = URL
    aTarget(iPublishListName) = List
    aTarget(iPublishListDesc) = Title
    lVer = -1 ' can't tell STS server version
    If Val(Application.Version) >= 12 Then
    lVer = Application.SharePointVersion(URL)
    End If
    End Sub

  4. Comment out this line lVer = Application.SharePointVersion(URL) by adding a single apostrophe ( ' )to the beginning of the line.
  5. Add a line immediately beneath the commented line that reads lVer = 2 (that's an L, as in lollipop). The section will now look like this (I have highlighted the changes):

    Sub Initialize(List, Title, URL, QuickLaunch)
    strQuickLaunch = QuickLaunch
    aTarget(iPublishURL) = URL
    aTarget(iPublishListName) = List
    aTarget(iPublishListDesc) = Title
    lVer = -1 ' can't tell STS server version
    If Val(Application.Version) >= 12 Then
    ' lVer = Application.SharePointVersion(URL)
    lVer = 2
    End If
    End Sub


  6. Click File, Save. You will get an error message that the file is Read Only. Click OK to continue. A Save As dialog will appear. Change the file type to Excel Add-in (.xla). When you do this, the file location will change to the Excel addin directory - you will want to save the file to somewhere you can remember (like the Desktop). Make sure you've named it EXPTOOWS.XLA.
  7. Close the VB editor and exit Excel.
  8. Using Windows Explorer, navigate to \Program Files\Microsoft Office\Office14\1033.
  9. Copy the new file (EXPTOOWS.XLA) from your Desktop (or wherever you saved it) into \Office14\1033. You will be prompted to confirm this action.
  10. When the new file is in the \Office14\1033 folder, you should be able to import the spreadsheet into Sharepoint 2010 (although you may have to restart IE or the Sharepoint session).
Some of the blog posts say that you can change the version in the line from lVer = 2 to lVer = 3 and try that too. In my tests, it didn't matter which version you used as long as it was in the version in the \Office14\1033 directory. A modified EXPTOOWS.XLA in the \Office12\1033 folder didn't seem to cause this any ill-effects either.

9 comments:

  1. The code in the file reads:

    Sub Initialize(List, Title, URL, QuickLaunch)
    strQuickLaunch = QuickLaunch
    aTarget(iPublishURL) = URL
    aTarget(iPublishListName) = List
    aTarget(iPublishListDesc) = Title

    End Sub

    It does not have the:

    lVer = -1 ' can't tell STS server version
    If Val(Application.Version) >= 12 Then
    lVer = Application.SharePointVersion(URL)
    End If

    Should I enter all of the code you have listed above?

    ReplyDelete
  2. We have a user that is experiencing this issue but my environment (SharePoint 2010 on Windows Server 2008 R2) does not have the folder "\Program Files\Microsoft Office\Office14\1033". There is a folder "\Program Files\Microsoft Office Servers\Office14\1033" but it does not contain the file in question and a search did not find it on the machine at all (as an administrator, with "Show Hidden Files and Folders"). The user having the issue is logging in to the SharePoint site using Forms Authentication - this does not appear to be an issue for users logging in using Window Authentication. Any ideas?

    ReplyDelete
  3. Never mind - read some other posts that clarified (for my pea brain) that this need to be done on the CLIENT not the server. Will try that if I can get access to the users system.

    ReplyDelete
  4. worked for me..IE8, Excel 2007, SharePoint 2007

    ReplyDelete
  5. I get error: "Cannot connect to the server at this time. Your table cannot be published."

    ReplyDelete
  6. Update: Figured it out. Amazingly this will only work when importing to the root site (not to a sub site). So import to root site, then save as list template, then create in sub-sites.

    ReplyDelete
  7. I make these changes but get the error "Object Variable or With Block variable not set". Any ideas?

    ReplyDelete
  8. Worked for me. SP 2010 & Office 2007. Thank you very much!!

    ReplyDelete