programmatically Set Reference to Library

  • Thread starter Thread starter Todd uttenstine
  • Start date Start date
T

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




.
Click to expand...
 
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)
 
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
 
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)
 
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
 
Back
Top