Office Automation of a specific version 1

[JackTheC]

I have 2 versions of MS Office installed.
When I use createobject("excel.application") the last version is opened.
But I want the previous version to be used for Automation.
How to specify the version of Excel (or Word) to be used for Automation?

When I uninstall the last version, the previous becomes the active one again, but I want to be able to manually use both versions.

 

[Mike Lewis]

in most cases, you can add a dot and a version number after the OlE classname in the CREATEOBJECT() call. For example:

oXL = CREATEOBJECT("excel.application[highlight #FCE94F].5[/highlight])

I haven't tested this in the case of Excel, but you can easily try it yourself.

EDIT: To find your Excel version number, go to the top-level Help page. You should see it listed under "About Microst Office".

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JackTheC]

Thanks Mike,
I just wanted to add that I looked in the Registry and i saw an entry Excel.Application.10 .
Then i tried that number .10 (in CREATEOBJECT("excel.application.10) but still the last version (16) was opened.
Excel.Application.10 and .16 had the same CLSID referencing to the same executable?

In the registry the exe of the 32 bit version 10 is stated in that CLSID under LocalServer while
the exe of the 64 bit version 16 is stated in that CLSID under LocalServer32.

Edit: Ah, that's funny: I manually swapped the names in the registry of the LocalServers mentioned above, and then the 10 version is started.
Swapped back and then the 16 version started again. But I can't do that in a distributed program. Too risky.

 

[Chris Miller]

I'd say something in the original state of your registry is wrong. If you install mutiple office versions the LocalServer32 entries should refer to the corresponding version, so you get Excel 10 from excel.application.10, anything else is broken or manipulated registry.

I'm not aware of any case where MS intentionally maps older versions to newer versions. You might think this is done with downward comaptibility in mind, so it would not really matter, but the instanciated server would not know it was called up by another ProgID.

Chriss

 

[JackTheC]

No Chris, I installed version 10 and 16 on several PC's the result is always the same. Same CLSID, and the entry in LocalServer32 is the last installed version whether it is 64 or 32 bit version of Office 16.
But only for Excel. AFAIK Word 16 has no automation entry.

 

[Chris Miller]

The last version will get the version independent ProgID Excel.Application, but the entries for Excel.Application.10 and Excel.Application.16 should point to their EXE, not the latest EXE. If something else happens, you should perhaps report that to MS.

Chriss

 

[Chris Miller]

[quote='JacktheC]the last installed version whether it is 64 or 32 bit version of Office 16[/quote]
Did you mean whether it is Office 10 or 16? The 32bit and 64bit verions of OLE automation servers also go into the 32bit vs 64bit version of the registry. You have to look for CLSIDs in the 32bit registry, if one of the office versions is 32bit, VFP will also only act on information of the 32bit registry.

Chriss

 

[JackTheC]

I can only add that version 10 is 20 years old...

I don't know what you mean by 32 bits version of the registry. Regedt32 and regedit have the same content.

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

and
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Excel.Application.10\CLSID
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Excel.Application.16\CLSID
have the same values

 

[Chris Miller]

That registry allotment of versionindependentprogid and others has been that way in the 90s already and was always that way. It does not make sense that Excel.Application.10 would point to the EXE of Excel 16 and MS doesn't upgrade an existing Excel installation as far as I know, or did the installation interview ask for that?

Chriss

 

[Chris Miller]

32 bits version of the registry:

https://stackoverflow.com/questions/869783/windows-64-bit-registry-v-s-32-bit-registry

You create an object by ProgID, not CLSID, so look for ProgId in the registry. Same applications of several versions can have the same CLSID, but that's not what 'Excel.Application', 'Excel.Application.10' and 'Excel.Application.16' are, they are ProgIDs and the one without a specific version number suffix is the VersionIndependentProgID and has an entry that will point to - I think - the version you installed last, whereas the Excel.Application.10 entry will point to Excel 10 and Excel.Application.16 will point to Excel 16. Or should.

If you have several entries in the 32bit registry that contradict the 64bit registry entries, I guess that's pointing out remniscent keys of previous installations.



Chriss

 

[JackTheC]

Chris, because
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Excel.Application.10\CLSID
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Excel.Application.16\CLSID
have the same value, they have the same ProgID since the CLSID determines the ProgID when you use CreateObject('Excel.Application.##')
ProgID is one of the properties of the CLSID, just like LocalServer32 is one.

 

[Chris Miller]

It's the ProgID that determines the CLSID, not vice versa.

The ProgIDs Excel.Application.10 and Excel.Application.16 are different, aren't they? They could point to the same CLSID. It's know for Excel 5 and 6 (97) that both Excel versions had the same CLSID, therefore you can't automate Excel 5 and 6 in parallel. See

https://www.dyalog.com/uploads/conf...excel_under_apl/html/mutipleexcelversions.htm

NOTE: Because Microsoft Excel 5.0 and 7.0 both use the same class name, when both are installed on the same computer, Excel.Application.5 always applies to Microsoft Excel 7.0. There is no way to prevent this behavior.

Mabye that's true for Excel 10 and 16, again.

You can already see from the very specific CLSID for Excel with several zeros, that MS reserves some special CLSIDs for their applications, so CLSIDs for MS Applications are not that random and unique.

I would ask in a specific MS forum, i.e. VBA what to do about that, but as far as it seems there is no solution.

The only thing I would think about is a manifest for regfree COM usage.

Chriss

 

[Chris Miller]

Okay, I just ttried to get at the anatomy of the registry entries and one point is clear, when you first look up the 'Excel.Application.10' that's finding a CLSID in one branch of HKEY_CLASSES_ROOTI just compiled my.exe with an application class that's olepublic and thus I find my.application here:

[pre]------------------------------------------------
Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\my.application]
@="my.application"

[HKEY_CLASSES_ROOT\my.application\CLSID]
@="{BA5.....}"

[HKEY_CLASSES_ROOT\my.application\NotInsertable]
------------------------------------------------[/pre]

And for that CLSID I then find the detail information:

[pre]------------------------------------------------
Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{BA5.....}]
@="my.application"

[HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{BA5.....}\Foxruntime]
@="VFP9R.DLL"

[HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{BA5.....}\Implemented Categories]

[HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{BA5.....}\Implemented Categories\{40FC...}]

[HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{BA5.....}\LocalServer32]
@="c:\\programming\\tek-tips\\my\\my.exe /automation"

[HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{BA5.....}\ProgId]
@="my.application"

[HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{BA5.....}\Programmable]

[HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{BA5.....}\TypeLib]
@="{275E9555-47DF-4D93-927C-ED8058590DAA}"

[HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{BA5.....}\Version]
@="1.0"

[HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{BA5.....}\VersionIndependentProgId]
@="my.application"
------------------------------------------------[/pre]

In the ProgID branch the ProgId "my.application" is repeated, so it confirms what I was searched for initially in HKEY_CLASSES_ROOT is represented by this CLSID and then the LocalServer32 key states what to run...my.exe

The registry branches may look slightly different for Office applications, but the main keys are the LocalServer32 stating what to run and the ProgId within the CLSID branch that matches the HKEY_CLASSES_ROOT\<<ProgId>>\CLSID key.

If two excel versions Excel.Application.X and Excel.Application.Y point to the same CLSID that will make both run the same actual EXE, even though the ProgId subkey of that CLSID will only match the inital ProgID you specify in your CREATEOBJECT(). That's I think what happens, and there's nothing you can do about that, as you don't determine CLSIDs.

It's not that way in general, that CLSIDs stay the same for different versions of an automation server, but if they do, the root entries in HKEY_CLASSES_ROOT can only point to one CLSID branch of HKEY_CLASSES_ROOT\WOW6432Node\CLSID and that will then only contain one ProgID sub key and one LocalServer32 key, so both version specific classes run the same EXE, which means the version specific class names become version unspecific.

You don't control how CreateObject() works and I think the VFP runtime also has no say when it comes to delegating OLE to find the LocalServer32 to run starting from a ProgID, how the lookup of that in the registry is done. It should be a generally known problem, I now also find another forum thread in excelforum.com stating Excel.Application.8 has the same CLSID as Excel.Application.9, so this seems to apply to all Excel versions and desppite version specific ProgIds they all point to the same EXE and whatever is specified for that CLSID runs.

So, yes, central point is the same CLSID. I can't search your registry for whether you find the version specific progids in other CLSIDs, though. I wonder why MS insisted on the constant CLSID for Excel.Application, I don't know how that is for other Office applications, but I think I ran different Word versions where that was necessary.

Chriss

 

[JackTheC]

Since there is no elegant way to force the 10 version, I opt for the (programmatical) name swap of the LocalServer entries. Then I can set 10 or 16 as the default for CreateObject('Excel.application') and still use the 10 and 16 version manually.

 

[Chris Miller]

I'd opt for another solution as presented by Joel Leach:

http://www.joelleach.net/2023/07/17/reg-free-com-with-out-of-process-servers/

(I should have added the link)

Local lcRun
lcRun = [Run /n "] + ("<<full path to>>\Excel.exe") + [" /automation -Embedding]
&lcRun

You don't get an object reference loExcel this way, what it does is registering that specific Excel in memory. After that the second part should find that Excel version:

#DEFINE MYCOMOBJECT_CLSID = "{00024500-0000-0000-C000-000000000046}"
loMyComObject = CreateObjectEx(MYCOMOBJECT_CLSID, GetEnv("COMPUTERNAME"))

and put together with another detail about LockSetForegroundWindow:

#DEFINE EXCEL_CLSID "{00024500-0000-0000-C000-000000000046}"
Local lcRun, loMyComObject 
DECLARE INTEGER LockSetForegroundWindow IN user32 INTEGER uLockCode
LockSetForegroundWindow(1) && lock  
lcRun = [Run /N "<<full\path\to>>\Excel.exe" /automation -Embedding]
&lcRun
loMyComObject = CreateObjectEx(EXCEL_CLSID, GetEnv("COMPUTERNAME"))
LockSetForegroundWindow(2) && unlock

Where you replace <<full\path\to>> with the full path to Excel.

Once that's run that Excel version will be known by the general Excel CLSID. So swapping out the EXE determines which version starts, without manipulating the LocalServer32 key.
All in all this also will work, if a user used any Excel version beforehand, because that doesn't start Excel with the /automation - Embedding command line switches, but the first run of an Excel.Application COM server will do the same and determine what COM server is known by that in memory registered COM class.

In short, make a first try after restarting your computer and see if it works at all. Joel has a tip if Excel wouldn't work by this command line switches to look for the commandline the task manager shows.
If your application needs to run both Excel versions for different features, in the long run it would be better to adapt all code running with the newer Excel, I think. I doubt you can easily switch between excel versions, as running the EXE with the /automation -Embedding switches triggers a CoRegisterClassObject() but running the other EXE doesn't unregister what the first run put into memory.

If you don't understand what I mean by that, you should now read Joels article and then come back.

Edit: Good news, it works with my own "my.application" COM Server, but the RUN /N part of the code causes a my.exe to remain in the task manger, even when releasing loMyComObject. I think that's not a big problem, though, and may not even be the same with Excel.Exe.

Chriss

 

[JackTheC]

Chris, that worked. Thanks.

I altered the code a bit and added the normal CreateObject for the standard version.
Now you see both ways next to each other.

Local lcRun, loMyComObject, EXCEL_CLSID,ea

EXCEL_CLSID="{00024500-0000-0000-C000-000000000046}"

* Forced version (10)
lcRun = [Run /N2 "C:\Program Files (x86)\Microsoft Office\Office10\EXCEL.EXE" /automation]
&lcRun

loMyComObject = CreateObjectEx(EXCEL_CLSID,'')
loMyComObject.cells(3,3).value=loMyComObject.version
loMyComObject.WindowState=3  && Max state = making excel visible

*** standard Excel Version (16)
ea=createobject("excel.application")
ea.workbooks.add
ea.cells(3,3).value=ea.version
ea.visible=.t.

Nevertheless for now I prefer the LocalServer name swap. That's a one time global setting without the need of adjusting any program.

 

[Chris Miller]

I prefer the LocalServer name swap. That's a one time global setting without the need of adjusting any program.

Wouldn't you need to swap it everytime you wanted another version, you only have one branch of HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{00024500-0000-0000-C000-000000000046}

You forgot the -Embedding switch in your version of Joel Leaches code, if it still works, that may be optional, but I wonder why. There's also really no need to use /N2 for minimized run of Office10\Excel.EXE the /automation and -Embedding whould make it run invisible, not evben appearing in the task bar, if both not works, then I wonder whether Excels COM automation class actually uses that mechanism at all. As Joel said you can find out once you start Excel be CreateObject('Excel.Application') and look for commandline in the task manager to see how OLE starts Excel.

Chriss

 

[JackTheC]

The -Embedding option does not work with Excel. The loMyComObject is not properly generated. The next lines gave errors. In the registry you can see that only /automation is used by MS.
I use /N2 to prevent the splash screen of Excel. This works fine. Starting Excel through Wscript.shells run command only works every second time. ???

BTW: There is an association problem with the 32 bit version of Office 16 in Win10 (Office 10 is always 32 bit here).
XLS (old) and XLSX are both associated with version 16. There is no way to associate XLS with version 10. Windows 10 simply does not allow it. This problem does not occur in Windows 11 nor with the 64 bit version of Office 16.

 

[Chris Miller]

Ok, and thanks. That rounds up the picture.

No idea why Wscript.shells run (I assume you still pass on /automation) is only working every second time, but if that's the pattern, you could of course always run twice, I don't see a reason to not use RUN /N", though.

And I do understand that if you only need to change it once the registry change works effortless, no codde changes.

Now the best thing to do, of course, is get rid of the Excel 10 dependency, is there something Excel 16 can't do and only Excel 10?

Chriss

 

[JackTheC]

There is another way to create an object reference to a specific version of Excel.
You can't use Excel.application.10 or Excel.application.16 because the have the same CLSID so they will start the same version.

But there are also Excel.Sheet.5 or Excel.Sheet.8 or Excel.sheet.12 ProgIDs in the registry.
.5 and .8 seem to point to Excel version 10 whereas .12 refers to Excel version 16.
We can make use of these:

Example:

declare SetForegroundWindow IN win32api INTEGER

* Create Excel.application object reference of a specific version
x = CREATEOBJECT('Excel.Sheet.8')  && or Sheet.5 or Sheet.12 or ...
y=x.application
x.close

* Create and fill sheet(s)
y.workbooks.add
y.cells(3,3).value='Version: '+y.version

y.windowstate=3  && maximize application
y.activeWindow.WindowState=-4137  && maximize and dock workbook
y.visible=.t.

SetForegroundWindow(y.hwnd)