Excel 2003-2007 compatibility problems in VB

[Will00]

Hi,

Currently I have a file (Excel 2007 in "Compatibility Mode") that copies a sheet to a new book and saves the new book as a new name and closes it. The problem I am having is that when I reopen the newly created wb, it tells me its in a new format different from excel?? However eventually I am able to open it. Other people who do not have 2007 cannot open it. my save as dialogue states ".xls". I think I have isolated the problem, which is that with 07 when you save as there are many options but I do not know the code for those options, can anyone help?? I need the file to save as compaitiblity...

Sheets("Version1").Select
Sheets("Version1").Copy
thisfile = range("b24").Value & ".xls": FileFormatNum = 56
directory = "G:\Documents\XL\2009\"

range("a11:ag24").Select 'This range has linked information
Selection.Copy
Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=directory & thisfile ' CODE BREAKS HERE
ActiveWorkbook.Close

 

[Norie]

Don't save it as 2007 compatibility or otherwise, save it in an earlier version.

To do that specify the FileFormat argument in SaveAs, just setting the filename extension won't do it.

If you don't specify the format the file will be saved in the format of the version of Excel being used, and I suppose that will be 2007 in your case.

 

[Will00]

Norie,

thanks for the reply, do you have any suggestions on how I can go about doing that? I have tried several methods with no avail

 

[Joe4]

Turn on the Macro Recorder and record yourself Saving the file, using Save As Type "Excel 97-2003 Workbook (*.xls)". You should be able to get the code changes you need to make from this recorded code.

 

[Will00]

Works like a charm when I try not to complicate code...thanks guys

 

[phlinux]

Dear all

Hopefully this is of use to someone, but in my case although Office 2007 opened my 2003 file in compatibility mode and saved it as a 2003 book, it did leave an Office 12.0 reference in the file. Under 2003 I unchecked this (as I was using no new functionality exclusive to 2007, but rather just adjusting the macro code within the usage parameters of 2003) and things worked fine.

phlinux