Changing Excel Version for Automation on the fly?

[sidefxboy]

Hi folks,

I'm wondering if someone could offer some suggestions for something.
I have some classes in .NET that can operate on Excel objects going
back to excel 97. In my unit testing I want to change the version that
gets created by CreateObject("Excel.Application") on the fly - so that
I can automatically run all tests using the excel8 object model, then
the excel10, then excel11, etc.

Can anybody suggest some way that this may be possible. Or any other
groups that I could ask in.

It looks like it may be possible through a bit of a hack - by
modifying the registry directly and changing HKEY_CLASSES_ROOT\CLSID
\{00024500-0000-0000-C000-000000000046}\LocalServer32 but I wonder if
there is an easier way.

The following has a bit more information about what I speak of above,
if it helps:

http://support.microsoft.com/kb/292491

But nothing that helps me

thanks,
Simon

 

[NickHK]

CreateObject can take an optional version indicator e.g. Excel.Application.8
and as it is a string, you can build it in a loop:

Private Sub CommandButton1_Click()
Dim i As Long
Dim XLApp As Object

For i = 8 To 12
On Error Resume Next
Set XLApp = CreateObject("Excel.Application." & i)
On Error GoTo 0

If Not XLApp Is Nothing Then
'.......whatever
XLApp.Quit
Set XLApp = Nothing
Else
Debug.Print "Excel verion " & i & " is probably not installed."
End If
Next

End Sub

NickHK

 

[sidefxboy]

That's what I thought, and was what I initially tried. But the
"Additional Notes" on this page:

http://support.microsoft.com/kb/292491

seems to indicate that this is not the case. It specifically warns
that doing it this way will not operate as expected. Quote:

"A common perception for Office Automation when you have multiple
versions of Office installed on a system is that you can dictate which
version loads by using a specific version-dependent PROGID (for
example, that "Excel.Application.9" loads Excel 2000,
"Excel.Application.10" loads Excel 2002 and "Excel.Application.11"
loads Office Excel 2003). However, this is not correct. Excel 2000 and
later versions of Excel share the same CLSID, so the version that
loads with these PROGIDs depends solely on which version was last
installed."

 

[Peter T]

No it's never worked for me either. If the objective is purely for testing,
by far the simplest way would be to open an instance of the Excel version
you want to test and ensure no other versions are open. then use GetObject.

There are other ways to get specific running Excel instances, if say all
versions are running, but not worth the effort just for this purpose.

Regards,
Peter T

 

[NickHK]

Yes, I remember now.
I have used such an approach on other components with success, but forgot
that it does not work with Office apps.

Actually, I have added an context menu entry to open Excel files in the
non-current version:

HKEY_CLASSES_ROOT\Applications\EXCEL.EXE\shell\Open in Previous XL
version\command
"C:\Program Files\Microsoft Office\Office 2000\Office\EXCEL.EXE" "%1"

You could add one for each version you have.
OK not automation, but easy to specify the Excel version you want to work
with.

Maybe if you