excel vba saveas not working - excel 2003 to sharepoint 2010 lib

Posted on 2011-05-03
Last Modified: 2012-05-11
Experts, I have a excel vba script that needs to saveas to a sharepoint 2010 library.  When the vba is executed from within excel 2007 or 2010, the saveas works correctly.  When using excel 2003, the users get a 1004 runtime error on the saveas.  here is the vba line:
ActiveWorkbook.SaveAs "" & Trim(filename), FileFormat:=56

where filename = 'filename.xls'

All users have read/write access to the lib.

I am guessing that there is a switch or something that needs to be set on the excel 2003 machines.  Any idea on what that might be, or any idea on why this won't work if it is not a switch?
Question by:jmathews1826
    LVL 29

    Expert Comment

    Try it without the File Format parameter.

    LVL 29

    Accepted Solution

    Take a look here for some discussion , especially this part:

            If Val(Application.Version) < 12 Then
                'You use Excel 97-2003
                FileExtStr = ".xls": FileFormatNum = -4143


    Author Closing Comment

    leonstryker, Thank You!!  Ironically, i saw a Excel memo on this regarding saving from within an excel 2007 vba and incorporated the file format number but never thought to look at this as a reason excel 2003 would not work.  I used the full IF test to look at versions, and act accordingly.  Thanks again!
    LVL 29

    Expert Comment

    Sure, no problem. Thanks for the grade,


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now