XL07 - How to conditionally determine Workbook file format compatibility in VBA?

[keeena]

I'd like to know how to invoke compatibility checker via VBA, or
similar functionality, in Excel 2007.

I searched around the Object Browser and Microsoft Team Blog for an
answer, but didn't find anything at this point (http://blogs.msdn.com/
excel/default.aspx).

I would like to do is save all Workbooks in my scripts as 95-2003
format (.SaveAs FileFormat = xlExcel8) whenever possible. So I need a
way to programmatically determine if the Workbook can be properly
saved in this format. I'd basically like to invoke the Compatibility
Checker via VBA. Or, alternatively, are there ways to conditionally
check the compatibility of the Workbook prior to saving?

This is all assuming a newly created workbook which has not been saved
yet. I would rather not have to create my own checks for every
possible 2K7 difference (e.g. data in rows > 64k, etc...)

TIA

 

[Ron de Bruin]

hi keeena

I have info here
http://www.rondebruin.nl/saveas.htm

 

[keeena]

Thanks for the info. However, I think it doesn't appear to solve my
problem (from what I could tell...please correct me if I missed
something from your link). I'm already aware of how to save a
workbook as a specific file type. What I need to do is be able to
*check to see if the workbook is compatible with a certain file type*.

I won't be interactively asking the user. My existing script creates
a new workbook and add data to it. Once the script is done, the
workbook may or may not be in a .XLS compatible fomat (I won't know
yet; the workbook is new...it does not have an extension). So I want
to create my own method to perform the Save operation. In this new
method, I want to determine if the Workbook is 97-2003 (e.g. .XLS)
compatible. If so, then save as .XLS. Otherwise, save as 2007 format
(.XLSX). I would ultimately use this new wrapper for all Excel Save
operations.

Is there any way I can do this somewhat easily? At all? I can't seem
to be able to trigger off any error/warning event. e.g.
programmatically saving a WB w/ 80,000 lines as FileType = xlExcel8
(97-2003 format) will work...just strips off lines >64k. I have
DisplayAlerts = False. If I set to True, I'm not certain of a way to
acknowledge the Compatibility Checker popup window and then
programmatically do something about it (e.g. SendKeys to close
it...yikes).

Why wouldn't Excel VBA expose a method to invoke Compatibility Checker
programmatically?? Arrgggg.

-K

 

[Ron de Bruin]

Hi keeena

I never looked for this.
If you use workbooks.add it will add a workbook with the format as your default file format in Excel.

I will test/try a few things today/tomorrw and post back

 

[keeena]

Thanks for the help...look forward to your reply. Also sent request
to Microsoft.

I hope it doesn't require jumping through hoops...like saving in 2
different formats, opening & comparing.

One thing I've found: a file with XL07 content saved can be saved with
a .XLS extension. Windows & Excel 07 will think file type is 97-2003
due to the extension. However, it will throw a warning upon opening
with XL07...XL07 thinks file is corrupted because content doesn't
match given extension. It will open OK w/ the XL07 content (e.g.
80,000 rows of data).

If its possible to catch this warning/error, then my Save method could
force-save w/ .XLS extension (no FileType specified) and then open the
file. If the warning message pops up, then I know the FileType needs
to be .XLSX.

What I don't know is if I can check for this error/warning. But
someone out there w/ more experience may know...

GL...hope you're able to find *something* that I may be able to use.

-K

 

[Ron de Bruin]

Hi Keena

I see no other way on this moment then use the CheckCompatibility dialog in the macro

Something like this that try to save as xls first and if you press cancel it save as xlsm
I try to find another way to test it without answering the dialog with Continue or cancel

Sub test()
On Error Resume Next
With ActiveWorkbook
.SaveAs "C:\Users\Ron\Test\aa.xls", 56
If .Name <> "aa.xls" Then
.SaveAs "C:\Users\Ron\Test\aa.xlsm", 52
End If
.Close True
End With
End Sub

I will look for more information this weekend
Maybe others have a better suggestion


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Thanks for the help...look forward to your reply. Also sent request
to Microsoft.

I hope it doesn't require jumping through hoops...like saving in 2
different formats, opening & comparing.

One thing I've found: a file with XL07 content saved can be saved with
a .XLS extension. Windows & Excel 07 will think file type is 97-2003
due to the extension. However, it will throw a warning upon opening
with XL07...XL07 thinks file is corrupted because content doesn't
match given extension. It will open OK w/ the XL07 content (e.g.
80,000 rows of data).

If its possible to catch this warning/error, then my Save method could
force-save w/ .XLS extension (no FileType specified) and then open the
file. If the warning message pops up, then I know the FileType needs
to be .XLSX.

What I don't know is if I can check for this error/warning. But
someone out there w/ more experience may know...

GL...hope you're able to find *something* that I may be able to use.

-K