Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-05-03
4
Medium Priority
?
1,554 Views
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 "https://dept.sp.xxx.com/sites/PPM/preproduction/vbom/VBOM%20Audit%20Files/" & 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?
0
Comment
Question by:jmathews1826
  • 3
4 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 35690683
Try it without the File Format parameter.

Leon
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 2000 total points
ID: 35690707
Take a look here for some discussion http://www.rondebruin.nl/saveas.htm , especially this part:

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

Leon
0
 

Author Closing Comment

by:jmathews1826
ID: 35691909
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!
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35692135
Sure, no problem. Thanks for the grade,

Leon
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

571 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