Programmatically Disable Default Combatability mode in Excel 2007

[Boswell]

I have an Excel 2007 workbook that contains a macro that performs calculations and exports results into a seperate "report" workbook. The initial creation of the report workbook is handled by the following code:

 Set reportWorkbook = Workbooks.Add

My workbook is now being used by users who have Excel 2007, but who's group policy is to open / save workbooks in (97 - 2003) compatability mode. This causes trouble in my export process due to Row and Column size differences between the 2007 .xlsx and the combatability mode files. Is there an easy way to circumvent the default compatability mode setting?

Thanks for any suggestions.

 

[xenou]

Hi,

If by row and column size differences you mean you have more than 65536 rows or more than 256 columns then the files cannot be opened in compatibility mode (without losing information).

Frankly, if the group policy is to use compatibility mode then you must send compatible files - that's what policies are for. If possible, just save the file as an xls and send it that way. Or ask about this - it's been nearly five years now since 2007 was released. Maybe the policy can be reconsidered.

 

[Boswell]

Thanks for your reply. The group policy is a relic from the migration from Excel 2003 to Excel 2007, and I am pursuing the "change the group policy route". Unfortunately, while the compatibility mode policy is obsolete(since the migration is complete), dealing with the bureaucracy associated with the group's IT team is an arduos process.

It is also required that the user have 2007 to interact with the workbook in any way (even its outputs), so there is no need to accomodate users with Excel 2003. Furthermore, all machines in the group run Excel 2007.

I was hoping for an alternative to dealing with a policy change.

 

[xenou]

I'm not sure. I think this is fairly stringent stuff you are dealing with - that's how policy rules are supposed to work (I found one description of this here: http://technet.microsoft.com/en-us/library/cc179081.aspx) I doubt you can get around it (barring serious attempts to directly undermine your IT department's rules, which is probably not worth it).

You said something about rows and column sizes - so I assume you mean 65536+ rows or 256+ columns. This is basically not allowed by the group policy so that's that. I'd advise you to work through open channels to address the policy if it's no longer a benefit. Otherwise, you must find other ways to send the data (xml, text files, access files, data on multiple sheets, or what have you).