2003 to 2007 save file to document share website

[Katieg]

Hi All,

I have recently upgraded from excel 2003 to 2007 and I am currently ensuring all the 03 vba code works in 07. And there is one point which I am completely stuck on.

In vba I am trying to save a workbook to a document share site. This code works fine in excel 2003 but in 2007 I get the error: "runtime error 1004", and line b is highlighted.

.''some copying and pasting code here...
.
line a: ActiveWorkbook.Save
line b: ActiveWorkbook.SaveAs docshare, FileFormat:=56

where docshare = http://docshare/otherparts/file1.xls

I have also tried closing the file down, then re-opening it and then saving it to the docshare, but that gives me the same error.

I have also tried doing a CopyFile to the docshare, but then I get other errors.

This code works perfectly well in 2003, what could be causing this in 2007?

Many thanks in advance for any help offered.

Katie

 

[Katieg]

Ok, I have found the source of the problem..

I am creating a file in excel 2007 and saving it as 2003 format. This produces a file >50Mb. Which is the size limit allowed to upload to the docshare.

When I create the same file in excel 2003 it creates a smaller file, <50Mb, so is allowed to be uploaded to the docshare.

So my question is now: How to create smaller files in excel 2007? They need to be in 2003 format so my colleagues can open them.

Why does saving them in an older format increase the file size so much??