Results 1 to 7 of 7

Thread: Add References Programatically
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add References Programatically

    Over the last year I have had significant problems in distributing excel solutions as many users are updating computers and software. I never know what version of Microsoft office they are using, often they will use xp and office 2003 at work, but use vista and office 2007 at home or on their laptops. Suggestions of using late binding rather than early binding have been explored but I would prefer a solution that is unbreakable across all versions without too many user prompts.

    I have searched discussions and examples about adding references from this and other forums: It is a little scarce on this topic but I have managed to put together a solution and some utilities to develop this further.
    (a practical example is below which is probably not good code as its very long winded and repeative but I understand it and it is quite quick) What I ask members for is some advice on improving the code (it does work in most cases)

    PS I am totally colour blind so please forgive the posting etiquete..

    The following code is set up in a workbook open event. It checks to see if access to vba is enabled in trust settings then looks for VBA Extensibility. Then add reference to MS Office and to MS Word >>>

    Private Sub Workbook_Open()
    Dim Response As VbMsgBoxResult
    'Test to ensure access is allowed
    If Application.Version > 9 Then
    Dim VisualBasicProject As Object
    On Error Resume Next
    Set VisualBasicProject = ActiveWorkbook.VBProject
    If Not Err.Number = 0 Then
    Response = MsgBox(" Programme Stop..Your current security settings do not allow the code in this workbook" & vbNewLine & _
    " to work as designed and you will get some error messages." & vbNewLine & vbNewLine & _
    "To allow the code to function correctly and without errors you need" & vbNewLine & _
    " to change your security setting as follows:" & vbNewLine & vbNewLine & _
    " 1. Select Tools - Macro - Security to show the security dialog" & vbNewLine & _
    " 2. Click the 'Trusted Sources' tab" & vbNewLine & _
    " 3. Place a checkmark next to 'Trust Access to Visual Basic Project'" & vbNewLine & _
    " 4. Save - then Close and re-open the workbook" & vbNewLine & vbNewLine & _
    "Click Yes to go directly to the security centre?", vbYesNoCancel + vbCritical)
    If Response = vbYes Then Application.CommandBars("Macro").Controls("Security...").Execute
    End If
    End If
    MsgBox "Security Settings valid, click OK to proceed"
    Call DelER
    Call AddReference
    Call AddER
    Application.AutomationSecurity = msoAutomationSecurityLow
    Application.ScreenUpdating = False
    Dim n
    Dim Sh
    n = ActiveSheet.Name
    For Each Sh In ActiveWorkbook.Worksheets
    Sh.Protect Password:= "******" UserInterfaceOnly:=True
    Next Sh
    Disclaimer.Show
    Application.ScreenUpdating = True
    Application.ThisWorkbook.Worksheets("Summary").Range("h19:i19").Value = ".xls"
    Application.ThisWorkbook.Worksheets("Menu").Activate
    Application.Calculate
    Application.ScreenUpdating = True

    End Sub

    Private Sub AddReference()
    Dim Reference As Object
    With ThisWorkbook.VBProject
    For Each Reference In .References
    If Reference.Description Like "Microsoft Visual Basic for Applications Extensibility*" Then Exit Sub
    Next
    .References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
    End With
    End Sub

    Sub AddER()
    Dim rVBReference
    Dim wbBook As Workbook
    Dim i As Integer
    Dim theRef

    Const stGuid11 As String = "{00020905-0000-0000-C000-000000000046}"
    Const stName11 As String = "Microsoft Word 11.0 Object Library"
    Const stoGuid11 As String = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"
    Const stoName11 As String = "Microsoft Office 11.0 Object Library"
    'version12
    Const stGuid12 As String = "{00020905-0000-0000-C000-000000000046}"
    Const stName12 As String = "Microsoft Word 12.0 Object Library"
    Const stoGuid12 As String = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"
    Const stoName12 As String = "Microsoft Office 12.0 Object Library"
    Set wbBook = ThisWorkbook
    'On Error GoTo Error_Handling
    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
    Set theRef = ThisWorkbook.VBProject.References.Item(i)
    If theRef.IsBroken = True Then
    ThisWorkbook.VBProject.References.Remove theRef
    End If
    Next i
    If Application.Version = 12 Then GoTo v12settings
    If Application.Version = 11 Then GoTo v11settings
    'Iterate through the collection of active external references in the VB-project.
    v12settings:
    With wbBook
    For Each rVBReference In Application.ThisWorkbook.VBProject.References
    If rVBReference.GUID = stGuid12 Then
    GoTo ExitHere
    End If
    Next rVBReference
    End With
    With wbBook
    For Each rVBReference In Application.ThisWorkbook.VBProject.References
    If rVBReference.GUID = stoGuid12 Then
    GoTo ExitHere
    End If
    Next rVBReference
    End With
    With wbBook
    'Create the external reference in the VB-project.
    .VBProject.References.AddFromGuid stGuid12, 1, 0
    .VBProject.References.AddFromGuid stoGuid12, 1, 0
    End With
    GoTo ExitHere

    v11settings:
    With wbBook
    For Each rVBReference In Application.ThisWorkbook.VBProject.References
    If rVBReference.GUID = stGuid11 Then
    GoTo ExitHere
    End If
    Next rVBReference
    End With
    With wbBook
    For Each rVBReference In Application.ThisWorkbook.VBProject.References
    If rVBReference.GUID = stoGuid11 Then
    GoTo ExitHere
    End If
    Next rVBReference
    End With
    With wbBook
    'Create the external reference in the VB-project.
    .VBProject.References.AddFromGuid stGuid11, 1, 0
    .VBProject.References.AddFromGuid stoGuid11, 1, 0
    End With
    GoTo ExitHere
    ExitHere:
    Set rVBReference = Nothing
    Exit Sub
    Error_Handling:
    MsgBox "Unable to create the reference as " & "Object Library" & vbCrLf & " is not available on this computer.Please contact technical support and state" & vbCrLf & " Missing Reference", vbCritical
    Resume ExitHere
    End Sub

    Sub DelER()
    Dim wbBook As Workbook
    Const stDescription As String = "Word"
    Const stDescription2 As String = "Office"
    Set wbBook = ThisWorkbook
    On Error GoTo Error_Handling
    With wbBook.VBProject.References
    'Delete the reference.
    .Remove .Item(stDescription)
    .Remove .Item(stDescription2)
    End With
    ExitHere:
    Exit Sub
    Error_Handling:
    If stDescription = "" Then Exit Sub
    MsgBox "The reference does not exist!", vbInformation
    Resume ExitHere
    End Sub

    'Utilities
    Function ListAllRefs()
    Dim my_ref
    For Each my_ref In ThisWorkbook.VBProject.References
    With my_ref
    Debug.Print .Name, .Description, .GUID, .FullPath, .IsBroken
    Debug.Print
    End With
    Next
    End Function

    Sub Grab_References()
    Application.DisplayAlerts = False

    On Error Resume Next
    Application.ThisWorkbook.Worksheets("GUIDS").Delete
    Application.ThisWorkbook.Worksheets.Add.Name = "GUIDS"
    Application.DisplayAlerts = True

    Dim n As Integer
    With ThisWorkbook.Worksheets("GUIDS")
    On Error Resume Next

    .Cells(1, 1) = "Name"
    .Cells(1, 2) = "Description"
    .Cells(1, 3) = "GUID"
    .Cells(1, 4) = "Major"
    .Cells(1, 5) = "Minor"
    .Cells(1, 6) = "FullPath"

    .Cells(1, 7) = "IsBroken"


    For n = 2 To ActiveWorkbook.VBProject.References.Count
    .Cells(n, 1) = ActiveWorkbook.VBProject.References.Item(n).Name
    .Cells(n, 2) = ActiveWorkbook.VBProject.References.Item(n).Description
    .Cells(n, 3) = ActiveWorkbook.VBProject.References.Item(n).GUID
    .Cells(n, 4) = ActiveWorkbook.VBProject.References.Item(n).Major
    .Cells(n, 5) = ActiveWorkbook.VBProject.References.Item(n).Minor
    .Cells(n, 6) = ActiveWorkbook.VBProject.References.Item(n).FullPath

    .Cells(n, 7) = ActiveWorkbook.VBProject.References.Item (n).IsBroken
    Next n
    End With


  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,978
    Post Thanks / Like
    Mentioned
    59 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Add References Programatically

    Eh, in theory using late-binding would mean the code should work across all versions.

    That's kind of the point of that method.
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Jan 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add References Programatically

    Thanks Norie, I do appreciate yor comments on using late binding, however it has its consequences particularly if users move betweeen different versions and back again as references get added automatically particulary references to the office application. another concern is that if I use early binding at development stage, with full access to the objects properties and methods, and then before distributon convert this to late binding with the CreateObject method very few of the constants and properties are recognised, for example:

    ThisWorkbook.Worksheets("sheet1").Range("summary").Copy
    Word.Selection.EndKey Unit:=wdStory
    Word.Selection.TypeParagraph
    Word.Selection.Paste
    Word.Selection.EndKey Unit:=wdStory
    Word.Selection.InsertBreak Type:=wdPageBreak

    do you know how I can find the late binding options for this little bit of copy code, never mind the formatting code!
    e.g wdStory as a selection unit does not have a binary equivelent or does it?

  4. #4
    Board Regular
    Join Date
    Jun 2010
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Re: Add References Programatically

    This sounds like something I could use too...

    I have a spreadsheet with
    2 x DTPickers shows: =EMBED("MSComCtl2.DTPicker.2","")
    1 x combo box shows: =EMBED("Forms.ComboBox.1","")
    2 x buttons shows nothing for formula
    - uses ADODB (to get data from database)

    Having library issues on other users computers - missing &/or broken libraries or a diff version of a library (ADODB) installed & causing errors.
    Per: http://www.mrexcel.com/forum/showthread.php?t=478929

    1 fellow's in particular giving errors just on Worksheets("Update").Select & .Range("....") & on combo box code.
    But works fine on others & I can see the worksheet/ranges/combobox exist & are named properly.

    Did you get any further enhancemnents?
    Do I need to tick the Extensibility Library?
    Do I need to remove ADODB & re-add it?

    Been testing with:
    Excel 2007 with Vista / Win7
    Excel 2010 with Vista

    Thnx
    WinVista + Off2010
    Win7 + Off2007

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,978
    Post Thanks / Like
    Mentioned
    59 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Add References Programatically

    shell i d

    I see I never responed to the OP's last post.

    If I had of I would have wondered about the referencing problems he mentioned - using late-binding should eliminate those.

    As for the question regarding constants, you should be able to find all the constants in the relevant application's library in the Object Browser.

    eg you'll find wdStory=6 in the Word Object Library etc

    Properties should not be a problem as long as you reference everything correctly.

    I'm not sure how exactly the code posted could be altered for late-binding because I don't know what it's doing, but it should be pretty straightforward.

    Anyway, shell l d, I think your question is a bit different and it might be worth starting a new thread.

    The first thing to do when having problems with references is goto Tools>References... and see if anything is marked as MISSING.

    As for the Extensibility library - I don't think you need that unless you plan on writing code with code.

    Perhaps if you explain, in a new thread preferably, what you are doing we can offer further help.

    PS Have you considered using a userform rather than controls placed directly on the worksheet?
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    Jun 2010
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add References Programatically

    Hi Norie, I'm interested in Samanco's code above & also in the option of late-binding (which I'm doing with ADODB). So am having the same issues as Samanco = Compatibility issues with diff versions of Excel.

    Anyhow yes I already did create my own thread, the link is in my post above & I just added another post to it. I've also posted it on about 3 other forums, hoping for a solution... as still having issues.

    There are no missing/broken libraries listed, hence why I'm confused due to errors still occurring.
    WinVista + Off2010
    Win7 + Off2007

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,978
    Post Thanks / Like
    Mentioned
    59 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Add References Programatically

    Well I can't help you with Samanco's code because I wouldn't actually recommend that route, ie adding references progrmatically.

    I know it can be done, but the whole kind of point of late-binding is if you use that correctly you shouldn't have to.

    Sorry I didn't notice the link - it wasn't clear it was to your own thread, I'll take a look..
    If posting code please use code tags.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •