Late Binding Problem between Excel and Project

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JJ - 21 Sep 2007 00:15 GMT

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!

Reply to this Message

Jim Thomlinson - 21 Sep 2007 00:42 GMT

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

Signature

HTH...

Jim Thomlinson

> I have an Excel file that imports data from a Project file. However,
> the users could potentially be on two different versions of Project.
[quoted text clipped - 14 lines]
>
> Thanks in advance!

Reply to this Message

Dave Peterson - 21 Sep 2007 00:51 GMT

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.

> I have an Excel file that imports data from a Project file. However,
> the users could potentially be on two different versions of Project.
[quoted text clipped - 14 lines]
>
> Thanks in advance!

Signature

Dave Peterson

Reply to this Message

Jim Thomlinson - 21 Sep 2007 07:04 GMT

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.

Signature

HTH...

Jim Thomlinson

> And to add to Jim's response....
>
[quoted text clipped - 26 lines]
> >
> > Thanks in advance!

Reply to this Message

Dave Peterson - 21 Sep 2007 12:42 GMT

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

> 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
[quoted text clipped - 40 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Jim Thomlinson - 21 Sep 2007 17:48 GMT

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...

Signature

HTH...

Jim Thomlinson

> Just to add...
>
[quoted text clipped - 48 lines]
> > >
> > > Dave Peterson

Reply to this Message

JJ - 22 Sep 2007 07:26 GMT

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!

On Sep 21, 9:48 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com> wrote:

> 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
[quoted text clipped - 60 lines]
>
> > Dave Peterson

Reply to this Message

Peter T - 23 Sep 2007 11:45 GMT

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

Reply to this Message

Jim Thomlinson - 24 Sep 2007 16:54 GMT

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.

Signature

HTH...

Jim Thomlinson

> Hi Jim,
>
[quoted text clipped - 108 lines]
> > >
> > > Dave Peterson

Reply to this Message

Peter T - 24 Sep 2007 17:35 GMT

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 just looked at Chip's. It looks only at XL.

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
[quoted text clipped - 114 lines]
> > > >
> > > > Dave Peterson

Reply to this Message