Excel 2003 and 2007 Compatibility

[nghtrain88]

Hey guys-

Right now i have a program that will automatically create a new workbook, populate it with some data, and then save it. The new workbook however is a ".xls" file since I'm using the VB editor in Microsoft excel 2003. When I try to run the same working program on a machine with Excel 2007 it crashes b/c it tries to create a ".xlsx" file and later in the program I refer back to the originally created workbook as a ".xls" file.

Now I know there is backwards compatibility with 2007 and you can create and work with ".xls" files in 2007 so my question is how can I ensure that when this program is run in 2007 it creats a ".xls" file everytime and not a ".xlsx"? It needs to be compatible with both versions of Excel.

I have tried appending & ".xls" to the end of the file_name variable I created that is used when the new workbook is created but that does not change the file extension when run in 2007. Any suggestions?

[datapard]

The only way I've found to do it depends on whether you are likely to create the file in both versions.

If you are then be prepared to have to change the code between versions. What I came up with is:

'    ActiveWorkbook.SaveAs Filename:="Filename.xls", FileFormat:=xlExcel9795 'if run in Excel 2003
    ActiveWorkbook.SaveAs Filename:="Filename.xls", FileFormat:=xlExcel8 'if run in Excel 2007

Hope this helps, and if anyone can give a better way of doing this that would be great.

[nghtrain88]

thanks for the reply.

I dont think i'll be able to use that seeing as i dont want the users to have to go in and edit code and I sure dont want to be editing the code everytime someone needs to run it on a different setup.

There's gotta be a way to specify to always create a ".xls" file no matter what. If i specify "Filename:="Filename.xls", FileFormat:=xlExcel9795 " will that always make the extension a ".xls" file even if its run in Excel 2007?

I am currently waiting for the upgrade to 07 or I would try it on my own but I only have 03 at the moment so I can't test it out yet.

[datapard]

thanks for the reply.

I dont think i'll be able to use that seeing as i dont want the users to have to go in and edit code and I sure dont want to be editing the code everytime someone needs to run it on a different setup.

There's gotta be a way to specify to always create a ".xls" file no matter what. If i specify "Filename:="Filename.xls", FileFormat:=xlExcel9795 " will that always make the extension a ".xls" file even if its run in Excel 2007?

I am currently waiting for the upgrade to 07 or I would try it on my own but I only have 03 at the moment so I can't test it out yet.

No, it won't. That's why the two lines of code. The first will run in 2003, the other in 2007.

In fact, I've got a question up on the forum myself as to how to detect which version of Excel you are running so that you can programmtically switch between the two lines of code. This, however, is what I have for now.

datapard

[westconn1]

apart from making microsoft rich, what is the point in upgrading to 2007 if you are always going to save as 2003 format?

if you always want to save as 2003 format, it should be possible to put code in the before save event to make sure that all workbooks based on a template are save to that format
or create an addin with the application withevents to cover all workbooks saved

[nghtrain88]

this is not a personal application. I'm writing this program for work and it will be used by people running both 03 and 07 since we are in the process of upgrading everyone but there are still those who are set in their ways and are going to refuse the upgrade and will continue working in 03.

not familiar with these before save and after save events.. can you elaborate?

As far as with events, how would I go about creating an addin that changes the filetype?

[nghtrain88]

Problem resolved. Datapard I took a look at your thread on performing a check to find out the version of excel with Application.Version and was able to use that to do what I need to do... thanks!

[datapard]

Glad I could help. The people here have helped me often enough, I'm glad that this time I was able to help someone.

datapard