Programatic Reference to Libraries

[Guest]

Is it possible to set a reference to a library programmatically like I
can do for an add-in?

Thanks in advance for any help.

 

[Chip Pearson]

Yes. If you know the name of the library file, you can use code
like the following:

ThisWorkbook.VBProject.References.AddFromFile _
Filename:="H:\whatever\typelib.olb"

If the typelib has been registered, and you know the GUID and
version numbers, you can use code like

ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{ whatever }", Major:=x, Minor:=y


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

This is an old thread, but here goes ...

Is it possible to retrieve the GUID for a a registered application?

I need to connect a Visio 2003 macro to Excel. Some of the folks that run
the macro have Excel 10, some have Excel 11. I'd rather not count on where
they happened to install the file, so I prefer to use addfromguid. But, I
can't figure out how to get the GUID on some arbitrary PC elsewhere in the
universe.

Is there a function that I can feed a string like "Microsoft Excel 10.0
Object Library" and retrieve a GUID that I can then feed to addfrom guid?

Thanks!
Ed

 

[NickHK]

Ed,
Look into using late binding rather than early binding/GUIDs.
http://peltiertech.com/Excel/EarlyLateBinding.html

Also, if you have to write to the VBA project, the user will have to allow
Tools>Macro>Security>trusted Sources> "Trust access to VBA Project".
For versions of Excel>9.0

NickHK

 

[Guest]

Thanks! That's the clearest description of early and late binding that I've
seen.

After posting the question, I poked around and came up with a hybrid late /
early binding solution that should work on the machines in question - and I
don't have to develop with early binding and muck with the code before I
release it so that late binding works.

If the reference to excel is busted (as seen in the .isbroken property), I
late bind an object to whatever excel is installed on the PC, look at the
..path property of the late bound object, build a likely file name for Excel
including the path, and use .adddFromFile to add the reference. Once it's
added, I can check versions & such for some rassurance that it's going to
work.

If all that fails to work, then it's no more busted than it was to start
with - and I've upheld the "First, do no harm." principal.

I suspect if I understood GUID's better, I could go down the .addFromGUID
path. But that, I think, is for another day.

Ed

 

[Guest]

Chip,

MUST the TypeLib be registered? (I'm assuming not but would like to verify)
Also, if applying this technique to MS Access, should the code be put into a
Class Module or does it not matter?

Further, if a component can be placed in a subfolder and referenced in code,
is there much of a performance improvement to write an installer or otherwise
get the component registered? Recognizing deployment is to varied
environments and versions.

Thank you very much.

Allen B.