[RESOLVED] Opening Specific Version of Excel

[Afshin]

I'm opening an instance of Excel to import data from a workbook using the following approach in VBA:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlWSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open(FileName, False)
Set xlWSheet = xlBook.Worksheets(1)

The problem is that the workbook is an Excel 2003 document, but the machine running the code has both 2003 and 2007 versions of Excel. The Excel instance opens the document in 2007 causing errors, which crash the code.

Is there a simple way of specifying in code which version of Excel to create an instance of?

[Siddharth Rout]

The problem is that the workbook is an Excel 2003 document, but the machine running the code has both 2003 and 2007 versions of Excel.

Both Cannot exist together without giving errors... you will have to uninstall one of them...

[Afshin]

Actually they exist together fine as long as I open the Excel version that I want first and then choose the document that I want to work with. Unisntallation is unfortunately not an option.

[westconn1]

you could shell with full path and filename, to open the correct version of excel then use getobject(filename) to to work with that instance of excel

alternatively you could create a shortcut to open that workbook in the correct version of excel

[si_the_geek]

Actually I think you can do it fairly easily, just change this line:

Set xlApp = New Excel.Application

to this:

Set xlApp = CreateObject("Excel.Application.11")

The number at the end can be one of the following:
7 - Excel 95
8 - Excel 97
9 - Excel 2000
10 - Excel XP
11 - Excel 2003
..and presumably 12 for Excel 2007, but I haven't checked yet.

[Afshin]

Thanks for the suggestions guys. The suggestion by si_the_geek should have worked, but it didn't. Makes me think the end users are doing something strange (works fine on my machine since I don't have 2007 on mine).

I'll let you all know how it turns out.

[RobDog888]

Yes, 12 is for 2007.

[anhn]

Install multiple versions of Excel (and Access too) on the same machine will give terrible headaches for novice users.

Specify version xx in CreateObject("Excel.Application.xx") won't help.

The culprit is even with 2 versions of Excel installed (2003=11 & 2007=12), the registry contains only one ProgID entry for Excel.

To switch it manually, run one of these shortcuts:
"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /regserver
or
"C:\Program Files\Microsoft Office\OFFICE12\EXCEL.EXE" /regserver

To switch it programmatically, you need to have a piece of code to change the value of this registry entry:

HKEY_CLASSES_ROOT\CLSID\{00024500-0000-0000-C000-000000000046}\ProgID

to either "Excel.Application.11" or "Excel.Application.12"

before using CreateObject("Excel.Application").

[Afshin]

Thanks for all the great suggestions. It works fine now. The problem came down to source files being sent to us as Excel 2.1 (don't ask me) and VBA was having problems opening them in Excel 2003. Having Excel 2007 on the same machine just muddied the waters even more.