compatibility with project 2000 and 2003 from VBA excel

[pleb]

Hi,

I have a Excel Macro which open an Mpp file. My macro is for many users, but
I don't know the version of MsProject, It can be 2000 or 2003.

I make reference in Excel with Tools \ References from VBA module menu, but
I think I can't select msproject 9 librairy and msproject 11 library.

How can we make a macro VBA code which can be used with 2000 and 2003 ?

thank's for your help.
PLEB.

 

[pleb]

A first solution is not use then application name in declaration, but only
OBJECT as this :

Public mspApp As Object (GOOD)

Public mspApp As MSProject.Application (NOT GOOD)

after that you can verify if msproject is in reference, and if not, you add
it as this :

Const msProject9Library = ":\Program Files\Microsoft
Office\Office\MSPRJ9.OLB"
Const msProject11Library = ":\Program Files\Microsoft
Office\Office11\MSPRJ.OLB"

projectVersion = "inconnu"
'vérifie si il y a bien une référence
For i = 1 To ThisWorkbook.VBProject.References.Count
If ThisWorkbook.VBProject.References.Item(i).Name = "MSProject" Then
projectVersion = "Version X"
If InStr(1, ThisWorkbook.VBProject.References.Item(i).FullPath,
"MSPRJ9.OLB", vbTextCompare) Then
projectVersion = "2000"
End If
If InStr(1, ThisWorkbook.VBProject.References.Item(i).FullPath,
"Office11\MSPRJ.OLB", vbTextCompare) Then
projectVersion = "2003"
End If
Exit For
End If
Next
' si il n'y e a pas on en ajoute une
If projectVersion = "inconnu" Then
'on n'a pas trouvé msproject on va ajouter la référence Recherche
Version Project
For i = 67 To 104
'Microsoft Project 9.0 Object Library
'C:\Program Files\Microsoft Office\Office\MSPRJ9.OLB
If FileExists(Chr$(i) & msProject9Library) Then
ThisWorkbook.VBProject.References.AddFromFile (Chr(i) &
msProject9Library)
projectVersion = "2000"
Exit For
End If
'Microsoft Project 11.0 Object Library
'C:\Program Files\Microsoft Office\Office11\MSPRJ.OLB If
FileExists("C:\Program Files\Microsoft Office\Office11\MSPRJ.OLB") Then
If FileExists(Chr(i) & msProject11Library) Then
ThisWorkbook.VBProject.References.AddFromFile (Chr(i) &
msProject11Library)
projectVersion = "2003"
Exit For
End If
Next
End If