SaveAS Filename compatibility problem

[davidm_uk]

I have a macro/VBA code that I've developed in Office 2010 Excel which is working ok, just need to sort out some cosmetics.

However I need it to run on earlier versions of Excel (back to Office 97 or Excel 2002), and when I test it on a 2002 version of Excel (on another PC) it's giving Compile errors. The particular one I'm struggling with is in:

Please Login or Register  to view this content.

where the FileFormat:=xlExcel8 throws up a compile error. There are few SaveAs statements throughout the macro.

The TempDataPathFile variable is "d:\Data\Test\Temp Data.xls" and the PC I'm developing on has Excel set to save in compatibility mode by default, and the file containing the macro is a .xls file.

How can I modify the code so that it will compile and run on different versions of Excel?

[Kaper]

I am not sure about 97 version (have handy 2000), but you can try xlExcel7 instead of xlExcel8. Anyway it is probably quite simple workbook - isn't it?

[davidm_uk]

Anyway it is probably quite simple workbook - isn't it?

Maybe by your standards it is but the 425 lines of it has so far taken me around 35 hours to get working, and still not finished. Very much a newbie with macros and VBA.

[Kaper]

I don't comment on the code complexity ;-)

It was only about the generated file - does it have complex structure and advanced (again not computation complexity but introduced with relatively new versions of excel) formula and formatting inside?
If the file is rather simple I suggested you can save it in even older format - like excel 7 (excel95).

To let code "recognize" what excel it is running on, you could use Application.Version

Have a look on such piece (having in mind that xlExcel8 is not recognized in older versions its numerical equivaklent 56 can be used) which saves in excel pre-2007 as standard format and starting 2007 (version 12) as xls:

Please Login or Register  to view this content.

[davidm_uk]

Ah, I misunderstood, sorry!

The generated file is VERY simple, just 5 columns of text and integers, no formatting and no formula. It's just being used as a temporary file to assemble the data which will be then copied to a couple of .csv files for importing as Gmail contact lists.

I didn't know about the iif function either, that might come in handy

Thanks, will post back after testing.

[davidm_uk]

The code above:

Please Login or Register  to view this content.

Runs ok in Excel (Office 2010) but in Excel 2003 fails with a compile error - Sub or Function not defined, with the iff highlighted. Looks like it's not supported in earlier versions.

[Kaper]

??? tested on Excel 2000. Works - may be iff is a problem? it is iif.
Test setup:

Please Login or Register  to view this content.

[davidm_uk]

I think I need a new eye test, or learned to read more carefully

iif does indeed work, although I did replace the "xlWorkbookNormalwith" -4143 after reading this:

http://www.rondebruin.nl/win/s5/win001.htm

Thanks Kaper