programmatically Set Reference to Library

[Todd uttenstine]

I need to programmatically set reference to Microsoft
Visual Basic for Applications Extensibility 5.3 Library.
How do I set a reference to this library through code?



Thank you

Todd Huttenstine

 

[Bob Phillips]

Todd,

Never tried it myself, but check out this tip from RB Smissaert
http://tinyurl.com/2zuqp

By the way, why can't you use late binding?

--

HTH

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

 

[Todd Huttenstine]

How would I do that?

-----Original Message-----
Todd,

Never tried it myself, but check out this tip from RB Smissaert
http://tinyurl.com/2zuqp

By the way, why can't you use late binding?

--

HTH

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




.

 

[Bob Phillips]

Show us the code and we'll have a go.

--

HTH

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

 

[Todd Huttenstine]

Sub Upgrade()
Dim VBComp As VBIDE.VBComponent
Dim Sfx As String

p = "Q:\CS Management Reports\Reports
Setup\Administrator Files"
f = "Administrator Setup.xls"
s = "Reports Setup"
a = "F6"

'MsgBox GetValue(p, f, s, a)
If GetValue(p, f, s, a) = 1 Then
'Code below if code does NOT equal 1
If MsgBox("An upgrade is available. Would you like to
upgrade now?", vbYesNo) = vbNo Then
With CreateObject("Wscript.Shell")
.Popup ("Program Setup Up-To-Date. Now quitting
application...") _
, 1, "Ops Reports Setup", 64
End With
Exit Sub
Else
'Code below if code DOES equal 1

'EXPORTING ALL MODULES IN A WORKBOOK
For Each VBComp In Workbooks
("ImportData.xls").VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Sfx = ".cls"
Case vbext_ct_MSForm
Sfx = ".frm"
Case vbext_ct_StdModule
Sfx = ".bas"
Case Else
Sfx = ""
End Select
If Sfx <> "" Then
VBComp.Export _
Filename:="Q:\CS Management Reports\Reports
Setup\Administrator Files\Exported Modules" & "\" &
VBComp.Name & ".txt"
'Filename:="Q:\CS Management Reports\Reports
Setup\Administrator Files\Exported Modules" & "\" &
VBComp.Name & Sfx
'Filename:=ActiveWorkbook.path & "\" &
VBComp.Name & Sfx
End If
Next VBComp

'Shows Program Update message box
With CreateObject("Wscript.Shell")
.Popup ("Program Update. Now quitting
application...") _
, 1, "Ops Reports Setup", 64
End With
End If
End If
End Sub

 

[Bob Phillips]

Todd,

All you need to do to use late-binding is to change

Dim VBComp As VBIDE.VBComponent

to

Dim VBComp As Object

You are already using late-binding on the WSH when you use

With CreateObject("Wscript.Shell")

(although I grant this could also be early binding if you have set a
reference, but you don't need to).

--

HTH

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

 

[Ivan F Moala]

To add the reference to the this

Sub RefExtensibityLib()
'// For Excel 2002+
'// You need to change your Security settings
'// to accept changes to the VBA

'\'if it already exits
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 0

End Sub

for an explaination see here

http://www.mrexcel.com/board2/viewtopic.php?t=8251