Check If COM Reference Available on Machine

[Matthew Wieder]

I have a VBA project which has a reference to the "Microsoft DTSPackage
Object Library" I have another workbook that opens the first one. Some
machines, that will be running this don't have the "Microsoft DTSPackage
Object Library" available since SQL wasn't installed, so I need a way
for the first workbook to test whether or not the reference to that is
in the list of available references before it allows the user to open
the second workbook. I have seen code on how to check if a reference is
broken, but the VBProject.Refenences collection only tests what
references are being used in the current project, not what references
are in the list as available. How do I do this?
thanks!

 

[Jamie Collins]

Matthew Wieder wrote ...

I have a VBA project which has a reference to the "Microsoft DTSPackage
Object Library" I have another workbook that opens the first one. Some
machines, that will be running this don't have the "Microsoft DTSPackage
Object Library" available since SQL wasn't installed, so I need a way
for the first workbook to test whether or not the reference to that is
in the list of available references

Remove the reference and use late binding e.g.

Public Function Test() As Boolean
Dim oPack As Object
On Error Resume Next
Set oPack = CreateObject("DTS.Package")
Test = Not CBool(oPack Is Nothing)
End Function

Jamie.

--

 

[Bob Phillips]

Jamie,

That's not the point is it? The guy wants to know if the dll is installed,
and if not, then he can exit graciously knowing the app won't work. If the
dll isn't installed, late binding doesn't work any better than early
binding.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Matthew Wieder]

I'm still looking for a solution if anyone has one...
thanks.

 

[Jamie Collins]

in message ...

That's not the point is it? The guy wants to know if the dll is installed,
and if not, then he can exit graciously knowing the app won't work. If the
dll isn't installed, late binding doesn't work any better than early
binding.

Bob, Isn't using using CreateObject a good way of finding out if the
dll is installed and allow for a graceful exit? Early binding would
result in compile errors if the dll wasn't installed, wouldn't it? I
am certainly open minded to missing the point here <g>.

Jamie.

--

 

[Bob Phillips]

You're right Jamie. It doesn't work any better, but it can be trapped and
tested for. I was having a blind-spot, as I had been thinking along the
lines of the VBE component references, and got blinded I think.

By the way, why do you test like

Not CBool(oPack Is Nothing)

against

Not oPack Is Nothing

Have you found a problem with the latter?


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Matthew Wieder]

Once I get to the second workbook, it's too late to call CreateObject
because it fail on pen since the reference is missing and the code won't
compile as you said. In the first workbook, I theoretically could call
CreateObject before I send the code over there, but then it's actually
going to create the object which we don't want to do. We just want to
know if the object is available.
thanks,
-Matt

 

[Bob Phillips]

Matthew,

I am not understanding what you mean by available then. As Jamie said, if
you early bind, if the dll doesn't exist, CreateObject will fail, it cannot
create an object when the dll is not there. Using late binding, the code
will compile, it will be a run-time error which is trappable.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

meant late bind not early bind.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jamie Collins]

...

By the way, why do you test like

Not CBool(oPack Is Nothing)

against

Not oPack Is Nothing

Have you found a problem with the latter?

Bob,
Two words: .NET <g> and the fact it tends to bite if one doesn't
explicitly cast. Having said that, (oPack Is Nothing) is fine in
VB.NET, even with Option Strict On, so I'm being ultra conservative as
usual.

Jamie.

--

 

[Bob Phillips]

Aaah!. Haven't used .Net too much yet, but I understand what you are saying.

Bob

 

[Matthew Wieder]

I don't want to change the second workbook to late binding (due to the
development advantages of early binding). So then I'm left with
attempting to create the object via late binding in the first workbook,
which I don't want to do, because if it succeeds, I have just created th
eobject, which has overhead involved.
thanks,
-Matthew

 

[Bob Phillips]

Matthew,

I would there are compromises required here, and only you can know which
will be acceptable. If none are, you may not be able to do what you want to
do.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Matthew Wieder]

So, there is no way to check whether a OOM object is available for use
without instantiating it? That's what we've concluded?

 

[Jamie Collins]

I'm left with
attempting to create the object via late binding in the first workbook,
which I don't want to do, because if it succeeds, I have just created th
eobject, which has overhead involved.

Out of interest, what sort of overhead are we talking about in real
terms, say worst case scenario and most likely scenario?

Thanks,
Jamie.

--