Detecting version of MS Office

Hi all

Recently distributed a small mdb to a client. Part of the app creates an Excel spreadsheet, works fine. However my client is using an earlier version of MS Office so the MS Excel object library version nn DLL is missing from the clients machine. Where my dev pc is using 11 theirs is 9.

So, is there an easy way to detect what version of Office is on the host machine? and if earlier is there a way of automating the reference switch in referrences?

David

The simplest way round this is to use Late Binding.

Can you elaborate please. As the initial binding is done before distribution.

There are some posts about it on this Forum. Also try Googling. This link may help. With late binding it doesn't link to Excel till runtime.

This thread may also help

Get what you mean. Looked at my code and the part that populates the Excel spreadsheet uses late binding, however, I have a function that checks to see if the named xls file is already open prior to attempting to populate it. This does not use late binding. I tried to change it to late binding but id did not detect that a named file was open. It only works with early binding.

With excel you can use


But that doesnt really help, quick google turned up:


For access

DCrake said:

This does not use late binding. I tried to change it to late binding but id did not detect that a named file was open. It only works with early binding.

Click to expand...

I jist discovered for late binding that some things are replaced by a number.

For example FileFormat:= _
wdFormatHTML

The wdFormatHTML wont't work without reference to MS Word Library but it does when you change it to

FileFormat:= _
8

So perhaps a search on what you are using with late binding in the search might get an answer. That is how I got the 8

Mike375 said:

I jist discovered for late binding that some things are replaced by a number.

For example FileFormat:= _
wdFormatHTML

The wdFormatHTML wont't work without reference to MS Word Library but it does when you change it to

FileFormat:= _
8

So perhaps a search on what you are using with late binding in the search might get an answer. That is how I got the 8

Click to expand...

If these values are used in several places it may make for clearer coding if you define them as a constant. Ie const wdFormatHTML = 8

Rabbie said:

If these values are used in several places it may make for clearer coding if you define them as a constant. Ie const wdFormatHTML = 8

Click to expand...

Good idea. And now is the time because so far I have only made one.

Mike375 said:

Good idea. And now is the time because so far I have only made one.

Click to expand...

Nothing worse than getting a useful suggestion when you've just changed 50 lines

That which "changes" with Late binding are just constants. (Sometimes as part of an enumeration).
They're defined for convenience in the target library - but none the less always just represent a literal value.
In the previous example wdFormatHTML is a constant in the Word object library who's value is 8.
If you're switching to late binding any such constants you've not replaced will fail at compile time (assuming you have Option Explicit always enabled - which, of course, you always should).
There's no effort to finding the constant value either.
In the immediate window
?wdFormatHTML
8
will return the literal value for use in your late bound code.

Though in unpredictable circumstances such as this, Late binding is the way to go - it's not always possible.
For example if you want to sink events of objects in the target library then you must use early binding.

To do this you can reference the earliest version of the library in your development copy of the MDB before releasing it. Access will step up to newer versions - e.g. a referenced Word 8 library will use 9, 10, 11 or 12. But, as amply demonstrated by this thread's existence not the reverse.
However that requires that you have an instance of the earlier version on your PC (a great advert to maintain multiple Office installations on development machines ;-)
If licensing permits, you could just grab a copy of the appropriate type library file and reference that (for example in this case it would be EXCEL9.OLB in the Office 2000 installation folder).

While references can be established via code - they can obviously only be done so in an MDB (ACCDB) and not in an MDE (ACCDE).
You'd need to be very sure you didn't reference any such code first and attempt to add your reference at application startup. (Access might well highlight the problem anyway at application startup - whether or not it caused any runtime or compile errors :-s)
With that and the MDE limitation - it's not something I care for. (Though the act itself of adding references through code is pretty straight forward).

Cheers.

LPurvis said:

There's no effort to finding the constant value either.
In the immediate window
?wdFormatHTML
8
will return the literal value for use in your late bound code.

Click to expand...

I must be doing it wrong. I put ?wdFormatHTML in View>Immediate Window and with cursor at the end of ?wdFormatHTML hit the enter key and nothing happened. I went to Immediate Window in no specific module.

If licensing permits

Click to expand...

, you could just grab a copy of the appropriate type library file and reference that (for example in this case it would be EXCEL9.OLB in the Office 2000 installation folder).

Click to expand...

So is A97 the one to get. I have enough A95s to make a frisbee factory but it is different.

I have only been doing this as full time thing for a couple of months and this part is a much bigger deal than making the stuff

For the value to be returned in the immediate window - it must be in a project which as a Word reference added yes?
Otherwise no value will be returned (well actually a ZLS, the immediate window doesn't raise errors - it creates the variable implicitly - which isn't much help).

And yes - I do tend to use 97 as a lowest common denominator.
Obviously in applications into a more predictable / controlled environment then this safety margin is not such a problem (there's maybe two versions out there at most).

LPurvis said:

For the value to be returned in the immediate window - it must be in a project which as a Word reference added yes?
Otherwise no value will be returned

Click to expand...

Works OK now.

And yes - I do tend to use 97 as a lowest common denominator.
Obviously in applications into a more predictable / controlled environment then this safety margin is not such a problem (there's maybe two versions out there at most).

Click to expand...

If you make something in A2000 on Office2003, then when it was put on the persons computer could the reference then be ticked on their Office 2000.

For some reason the A2003 on this machine now defaults to A2002-2003 instead of the 2000. I might have worn it out

I just tried it on 3 other computers and they all defaulted to A2000. Each machine has a different copy.

You can indeed manually (or as I mentioned, via code) add and delete references to a project.

Certainly a user could check an Office 2000 application reference in the VBA project of your application before using it or distributing it to other users at their location.
I feel it would be a terrible practice though lol.
(And would prevent any of the advantages of distributing MDEs).

Refering to the 2000 / 2002-2003 default - I presume you're talking about default file format when creating new MDBs in Access?
That's specified by the setting in
Tools > Options > Advanced > Default File Format.

Cheers.

LPurvis said:

(And would prevent any of the advantages of distributing MDEs).

Click to expand...

I have only just started with the MDE and still get caught. Aorund Christnmas I thought I made a great "trial period" system that was based on chaning the value on the OnClick line to Date()*86400 and of course that requires the form to be opened in design view. After I blew my trumpet HiTechCoach pointed out...but it will be MDE

Refering to the 2000 / 2002-2003 default - I presume you're talking about default file format when creating new MDBs in Access?
That's specified by the setting in
Tools > Options > Advanced > Default File Format.

Click to expand...

That was it.

I do some pretty wild clicking about but I have no idea how I got there to change it to 2002-2003

Cool.

(Some more text here just to allow me to post "Cool" !! :-s)