Excel: Automatically fix References to Microsoft Project

[metaldogo70]

I have an Excel spreadsheet that imports data from selected Microsoft Project files. However, some of the users of this spreadsheet use different versions of Microsoft Project.

Is possible to programmatically check for which version of Project the user has installed and and set the appropriate reference for the Excel spreadsheet?

Thanks!
JJ

[TonyJollans]

Hi metaldogo70,

Welcome to VBAX!

If you set a reference to the earliest version that anybody has, it should automatically use a later version if that's what's on the target machine.

Alternatively you can use late binding and not have to worry about it.

The answer to your question is, I think, yes it is possible but it's a bit of messing around and it shouldn't be necessary.

[metaldogo70]

I have Microsoft 2000 and the spreadsheet fails for anyone using Microsoft 2002 and 2003. Typically, I have had to manually de-select the "MISSING" reference and select the available DLL associated with the installed version of Microsoft Project (2002 or 2003).

What is late binding and how do I do it?

[TonyJollans]

Coding for late binding means not referring to any project objects by name. Declare objects as type Object, and don't refer to any constants in the project library, for example, instead of coding[vba]Dim appProject As MSProject.Application
Set appProject = New MSProject.Application
appProject.Projects.Add
' etc.[/vba]code instead[vba]Dim appProject As Object
Set appProject = CreateObject("MSProject.Application")
appProject.Projects.Add
' etc.[/vba]Note that you can still refer to properites and methods (as in Projects.Add above) - it's just the object and constant names you need to avoid.

When you code this way you don't need the reference and everything gets resolved at runtime (late) rather than at compile time (early binding).