VBA - Create and manipulate new instance of Excel 2007

[mamboq]

Is there any way to create an instance of the Excel 2007 application using VBA in Excel 2003? That is, if I have a workbook open in Excel 2003, can I use VBA in that workbook to run Excel 2007 and manipulate a worksheet?

I tried using:
Dim xl2007 As Excel.Application
Set xl2007 = CreateObject("Excel.Application")

But that just creates another instance of Excel 2003, and I don't know how to make the CreateObject run Excel 2007 instead.

 

[mamboq]

Ok... I've been able to achieve part of what I want, but still have a problem.

The following code runs Excel 2007 from within Excel 2003, opens a workbook, then uses GetObject to reference the Excel 2007 application:

Dim x7 As Excel.Application, x
x = Shell("C:\Program Files\Microsoft Office\Office12\EXCEL.EXE C:\Book2007.xls", 1)
Set x7 = GetObject("C:\Book2007.xls").Application

My problem is that I cannot set a reference to the Excel 2007 Object Library in 2003 because Excel 12.0 doesn't appear in the reference list.

Is there any way to reference the Excel 2007 (12.0) Object Library in Excel 2003? Seems easy enough in Word 2003 because 'Microsoft Excel 12.0 Object Library' appears in the reference list.

 

[mamboq]

I was told by someone in another forum I should browse to locate the Excel 12.0 Object Library. Does anyone know what file I should be looking for and where it is located? I located the EXCEL.EXE file in the Office12 folder, and tried adding it as a reference, but it didn't work - no error was generated, but it wasn't added to the list of references.

 

[Jaafar Tribak]

I was told by someone in another forum I should browse to locate the Excel 12.0 Object Library. Does anyone know what file I should be looking for and where it is located? I located the EXCEL.EXE file in the Office12 folder, and tried adding it as a reference, but it didn't work - no error was generated, but it wasn't added to the list of references.

I believe it's the olb file XL5EN32 under the OFFICE12 folder but i don't think you can add a reference to it as the Excel2003 object library which is referenced by default has the same name and would cause ambiguity.

I can see two workrounds :

1- Use late binding by declaring "Dim xl2007 As Object"
2- If you are opening an 2007 workbook in the newly created Excel application (2007) and you want to automate that workbook you can use the Run Method from XL2003 to execute already existing code in the target xl2007 workbook .

Regards.

 

[Jaafar Tribak]

Also, i've just been checking the Registry for the CLSIDs of Excel2003 and Excel2007 and they are the same ie: "{00024500-0000-0000-C000-000000000046}"

This makes sense as COM CLSIDS are supposed to be version independent.

Regards.

 

[mamboq]

Thanks so much for your help Jaafar!

Option 1 seems to be working ok - that is, Dim xl2007 as Object, instead of as Excel.Application. I don't get intellisense doing that, but could write my code in Word (which allows me to reference the Excel 12 Object Library) and put the code back into Excel 2003.

Unfortunately option 2 is out of the question for me - at work we had Excel 2007 installed without Visual Basic for Applications. I nearly went insane when I found out, because it means we can't use our Excel files and templates that have macros or activex controls or even standard add-ins - we can therefore only use them in Excel 2003.

I asked our IT department if we could have the VBA installed, but they said to do that we would have to purchase an additional VBA license from Microsoft. Can't understand that - we have already purchased the license to use Excel 2007, but need to purchase another license for VBA?? It just doesn't make sense to me, especially considering VBA is categorized as one of the "Office Shared Features" according to the installation.