Late Binding Problem between Excel and Project

[JJ]

I have an Excel file that imports data from a Project file. However,
the users could potentially be on two different versions of Project.
With early binding this created a problem for those who used the
earlier version of Project. Therefore, I removed the reference and
used late binding.

This now created a problem in a function where I used constants
defined only in Project, but if there reference doesn't exist then the
constants mean nothing. The function relies on these constants for it
to work.

Questions:
1) I want to continue to use the late binding method, but how do I
allow my function to access the Project constants?
2) Is it possible to detect the version of Project that a user has
installed and programmatically create the reference?

Thanks in advance!

 

[Jim Thomlinson]

No it is not possible to detect the version and then make the reference. The
reference must be declared at design time (early) and not at run time (late).
So you are correct to use late binding. Now your only issue is the constants.
Since a constant is just a number you could replace the constant with the
number or better yet declare your own constants that match the project
constants. For example word has a constant wdBorderBottom which is -3. So
create a module called modWordGlobals and add

public const wdBorderBottom as long = -3

 

[Dave Peterson]

And to add to Jim's response....

I don't use Project, but when I need the value of an MSWord constant, I'll open
MSWord.

Go into its VBE, show the immediate window and type this:
?wdMarkupRectangle
and see this returned:
2

If I have lots to look up, I'll go into the Object browser and search.

 

[Jim Thomlinson]

I have a spreadsheet that reads the constant values out of XL, Word and other
MS apps. Regretably I do not have a link to MSProject. It is based on
http://support.microsoft.com/kb/239930
I can send you a copy. You need the dll from VB6 to make it work.

 

[Dave Peterson]

Just to add...

Chip Pearson has one for excel (I don't know if it works in different office
applications, though).

http://www.cpearson.com/excel/download.htm
Look for XLConst

 

[Jim Thomlinson]

I just looked at Chip's. It looks only at XL. Using mine you select the app
and version and it returns the constants... Mine needs a little more work as
it can return duplicates but the jist of it is there...

 

[JJ]

Thank you all for all of your responses! I already created a list of
the MS Project constants on a separate, hidden worksheet. However, I
may just declare all of the constants as my own to ensure they are
available at all times. It's just going to be extremely tedious
because the constants represent individual fields for Task and
Resource data. Therefore, there is going to be hundreds of constants
that I am going to have to declare. If anyone can think of any easier
way around this, please let me know. Thanks again!

 

[Peter T]

Hi Jim,

Earlier you said yours required a VB6 dll, what might that be and why not
something like this in VBA

Sub testTLI()
' Tools > References
' scroll down and check "TypeLibInformation"
' if not found Browse to TLBINFO32.DLL
Dim sPath As String
Dim sLib As String
Dim sFile As String
Dim i As Long

Dim tli As TypeLibInfo
Dim ci As ConstantInfo
Dim mbr As MemberInfo

'change to appropriate Office path
sPath = "C:\Program Files\Microsoft Office2K\Office\"
sLib = "Msword9.olb" 'Word 2000 library
' sLib = "Excel9.olb" 'XL2K

Set tli = TypeLibInfoFromFile(sPath & sLib)

For Each ci In tli.Constants
For Each mbr In ci.Members
i = i + 1
Cells(i, 1) = mbr.Name
Cells(i, 2) = mbr.Value
Next mbr
Next ci

End Sub

Regards,
Peter T

I just looked at Chip's. It looks only at XL. Using mine you select the app
and version and it returns the constants... Mine needs a little more work as
it can return duplicates but the jist of it is there...

 

[Jim Thomlinson]

That is the reference that you need (TLBINFO32.DLL). If I am correct that is
not a standard DLL that everyone is going to have. I do know that it comes
with VB6 (as per this article http://support.microsoft.com/kb/239930). My
code is basically the same as that except that I have listed all of the paths
and file nems for the standard office applications to make it easier.

 

[Peter T]

Ah, afraid I wrongly assumed everyone had it!

Not sure how I got mine, with VB6 perhaps but I think I've had it for ever.

I've just had a quick look too. I think merely need to change -
'ThisWorkbook.VBProject.References("EXCEL").FullPath'
to the path of the *.olb in question

eg, as in quick demo I posted, mine for Word2K was
"C:\Program Files\Microsoft Office2K\Office\Msword9.olb"

Regards,
Peter T

That is the reference that you need (TLBINFO32.DLL). If I am correct that is
not a standard DLL that everyone is going to have. I do know that it comes
with VB6 (as per this article http://support.microsoft.com/kb/239930). My
code is basically the same as that except that I have listed all of the paths
and file nems for the standard office applications to make it easier.