incorrect saving due to incompatability in versions

[tryer]

I have written some VBA in excel 2003 part of the code opens new workbooks and later saves them using the code below:

Please Login or Register  to view this content.

then later using:

Please Login or Register  to view this content.

when run in 2003 this works fine. However when it is run with office 2010 the saved file is corrupted. Presumably this is because office 2010 is being forced to save files with .xls extension. I could of course change the code to save with .xlsx format but then no doubt it would have problems when run on other machines running 2003.

So ideally I need a way to cover both. Is there for VBA to return the current version, if so I could change the code to
If (version 2010) then save as..........etc.

Or is there a better way?

[shg]

The SaveAs method includes a FileFormat argument to control the type of file.

In creating the name, leave off the extension; it will be added to the file name correctly by type.

Application.Version will tell you what version of Excel you're running.

Additional FileFormat enumerations were added in 2007 (as you'd expect). Those values are unknown to earlier versions of Excel, so you need to declare them as constants if you ever intend to use the code in earlier versions, so you don't get compile errors.

[tryer]

shg,

Thanks for the answer. Unfortunately it doesn't quite work.

I have removed the file extension as you suggested and when run in excel 2003 it does indeed save the files as .xls format. However running it in 2007 caused the files to be saved without any extension

(I have also noticed that the column width formatting is lost when running in 2007 though I think perhaps I'll post that as a separate topic if I find nothing through a search)

[tryer]

is there any reason why I can't (or shouldn't) use this:

Please Login or Register  to view this content.

[shg]

What did you use for the the FileFormat argument?

[tryer]

shg,

I didn't use anything. I'm sorry I must have misunderstood your instructions I thought you meant to leave it out altogether (which is what I did)

[shg]

No, I meant you should use it.