Converting 2003 file 2007 on the fly

[Andrew-R]

Hi all, I've actually got a proper coding question, for a change.

I'm using a data handling application which exports data to Excel, producing an Excel 2003 worksheet. Unfortunately this means that it gives an error if you try to export more than 256 columns of data (which I do every so often).

I've found a way to make the other appliction split the columns over multiple Excel sheets and I have a macro which merges all of the individual sheets back onto the front sheet.

However, before I can run my macro I need to:

1. Save the exported workbook as an Excel 2007 xlsx file.
2. Close the workbook
3. Re-open the xlsx file.

If I don't do this then my macro fails when the front sheet column count reaches 256.

So, what I'm wondering is:

1. Is there a programmatic way to tell Excel that it's no longer dealing with a 2003 format file and it can safely add columns above 256 or

2. Is there a way to tell if the target workbook is a 2003 file which has just been saved as a 2007 file (in which case my macro can close and re-open it) or if it is a genuine 2007 xlsx file?

Any advice greatly appreciated.

Andrew

[Andy Pope]

Use can use the FileFormat property to determine type.

Please Login or Register  to view this content.

see XlFileFormat Enumeration for complete list of available types.

Do you have control over the process that creates the initial workbook?

[romperstomper]

Check the workbook's Excel8CompatibilityMode property.

[Andrew-R]

Do you have control over the process that creates the initial workbook?

The process is:

1. An MR specific application
2. Based on JET database engine
3. French

What do you think?

[Andrew-R]

Check the workbook's Excel8CompatibilityMode property.

Perfect. Thanks Romperstomper.

[Andy Pope]

Given the French software only kicks out xl2003 file format is the question sort of irrelevant.
You always need to open the exported file and saveas to convert to xl2007 format.

[Andrew-R]

Yes, I was just wondering if, having saved as 2007 format, there was a way to make Excel treat it as 2007, without closing and re-opening it.

[Andy Pope]

No, even if you do the conversion manully via the Convert button Excel will still need to close and open the file before the extra columns appear.

[Andrew-R]

I feared as much, but the Excel8CompatabilityMode property is enough for me to automate the save/close/re-open process.