Importing Excel 2007 spreadsheets into WSS 3.0 using Vista

Importing Excel 2007 spreadsheets into WSS 3.0 using Vista

This is a pretty good example of what IT consultants do all day and why people are willing to pay us to do it for them.

Goal: create a Windows SharePoint Services application via the Internet based on a Microsoft Excel spreadsheet. Simple enough, right?

Process: Start with basic procedure described here. From WSS home page, click Site Actions button and choose Create. Under Custom Lists, click Import Spreadsheet. Fill in Name and Description boxes, click Browse and navigate to file location. Click Import.

Problem #1: you forgot to close your spreadsheet before importing it:

Close the spreadsheet. Continue to select data range as described in article linked above.

Click Import.

Problem #2: You’re using Excel 2007, not Excel 2003. You get an error:

Note: you may not get this error if you are doing the import on a local network; that is, it may be Internet-specific. I’m not sure yet. Anyway, enter the text of the error message into Google. Find this solution.

Cancel the import procedure. Locate and open EXPTOOWS.XLA.

Problem #3: Microsoft doesn’t trust its own code:

Click Enable Macros. Press Alt-F11 to see the code. Open the publishForm (Code) window. Edit the code by commenting out lVer = Application.SharePointVersion(URL) and adding lVer = 2 underneath. (That’s a lower-case “L,” by the way.)

Save the file.

Problem #3: You can’t save the file. It’s read-only:

Cancel the Save As action. You don’t want to save it as a different file. Close EXPTOOWS.XLA. Open its folder location (probably C:\Program Files\Microsoft Office\Office12\1033). Right-click the filename and choose Properties so you can uncheck the Read-only box.

Problem #4: It’s already unchecked:

Scream at the computer, “What do you mean, it’s not read-only? You just TOLD ME it was read-only!!!!” Take a deep breath. Open the Security tab. Note that Administrators have full control of this file, but as a lowly “User” in Vista, you have only Read / Read & execute rights:

Curse Microsoft for creating the bug in the first place and then thwarting your efforts to fix it. Click the Edit button so you can give yourself the permission to modify your own damn file. Hit Continue to get past the annoying User Access Control prompt (can’t show you that one — the screen capture program is disabled while that prompt is displayed). Assign Full Control for Users (hey, maybe you get get away with just Modify rights but at this point why take the time to find out?).

Open the .XLA file again, clicking through the same annoying warnings. Try to save it.

Problem #5: You still can’t save it. You must have had some other spreadsheet open:

Get completely out of Excel. Open the file again, make the change again, try to save again. Get the same viking error. Ask yourself what the hell is going on. Close Excel. Go back to Internet Explorer and close the SharePoint window you had open to do the import. Try again. No, that wasn’t it either. Close all the programs you have open — all of them! Try again. Do I even have to say it? Same error. Reboot your computer. Try again. Fail again. Go back and read the error message closely. Wait a minute … It’s not a file that Excel can’t access, it’s the entire viking 1033 folder! Why didn’t it just say so? Give Users full control of the 1033 folder, too.

Try saving again. Don’t be too suprised when it doesn’t simply work, but instead you get another security warning:

Consider getting a Mac, but think better of it. Click “Save changes and discard signature,” hoping this doesn’t come back to screw you later on. Close Excel. Go back to your SharePoint window. Try the import process again. View the results:

Like I said, simple enough.

Posted in All, Office 2007, SBS, Server 2003, SharePoint, Software, Technology, Vista on Jul 13th, 2007, 2:37 pm by David Schrag   

9 Responses

  1. September 5th, 2008 | 2:06 pm

    Thank GOD! You are my hero. Seriously, what the hell.

  2. Tweedledum
    January 26th, 2009 | 8:22 am

    Post was going great but it ended too quickly, i am at the error stage of cannot connect to server after i change the EXPTOOWS.XLA file and try to upload the excel file. I have tried different work arounds, Microsofts answer for the cannot connect to server error is to disable anonymous access which is already disabled so is clearly not the answer, i just want it to work, is there a devision at microsoft that comes up with ways just to annoy the user by making extremely simple tasks complicated and stressful?

  3. Jennifer
    July 8th, 2009 | 9:01 am

    This was a lifesaver. Thank you!

  4. Mike
    January 26th, 2010 | 4:31 am

    Perfect instructions. You rock!!

  5. February 12th, 2010 | 4:34 pm

    Just what I was after – thanks!

  6. harshitha
    April 29th, 2010 | 8:51 am

    thanq very much perfect solution

  7. Paul D
    June 29th, 2010 | 2:48 pm

    At problem #3, I have the Trust All From Publisher and Enable Macros buttons greyed out, but I can Show Signature Details. It shows a root CA that is not in my list of trusted root CA’s. If I click Disable Macros, nothing opens. My list of trusted root CA’s have several from Microsoft, but evidently not the one used for signing the exptoows.xla!

  8. Charlie
    October 14th, 2010 | 12:16 pm

    Very good post. This worked perfect up to the last step where I received a “cannot connect to server…” error. I followed the following KB as a work around:
    http://support.microsoft.com/kb/838703

    Thanks

  9. Heather
    December 21st, 2010 | 8:16 pm

    You were the one link out of too many to count to actually explain what the hell I was seeing for two days. Thank you so much.

Leave a reply