Results 1 to 6 of 6

Automatic add reference - Not working

This is a discussion on Automatic add reference - Not working within the Excel Questions forums, part of the Question Forums category; Hello, I have a workbook that adds an automatic reference to an external database (Microsoft ADODB Version 6.1). The problem ...

  1. #1
    Board Regular
    Join Date
    Aug 2012
    Posts
    54

    Default Automatic add reference - Not working

    Hello,

    I have a workbook that adds an automatic reference to an external database (Microsoft ADODB Version 6.1).

    The problem is that when I attempt to run the macro on another computer, it fails. Unfortunately, I don't get an error message and Excel crashes.

    I noticed that if I delete the code below (that sets up the reference) and manually set up the external reference, the macro runs as intended. Any idea what is causing Excel to crash?

    Code:
         
         'Macro purpose:  To add a reference to the project using the GUID for the     'reference library
    
    
        Dim strGUID As String, theRef As Variant, i As Long
    
    
         'Update the GUID you need below.
        strGUID = "{B691E011-1797-432E-907A-4D8C69339129}"
    
    
         'Set to continue in case of error
        On Error Resume Next
    
    
         'Remove any missing references
        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
    
    
         'Clear any errors so that error trapping for GUID additions can be evaluated
        Err.Clear
    
    
         'Add the reference
        ThisWorkbook.VBProject.References.AddFromGuid _
        GUID:=strGUID, Major:=1, Minor:=0
    
    
         'If an error was encountered, inform the user
        Select Case Err.Number
        Case Is = 32813
             'Reference already in use.  No action necessary
        Case Is = vbNullString
             'Reference added without issue
        Case Else
             'An unknown error was encountered, so alert the user
            MsgBox "A problem was encountered trying to" & vbNewLine _
            & "add or remove a reference in this file" & vbNewLine & "Please check the " _
            & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
        End Select
        On Error GoTo 0

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    71,199

    Default Re: Automatic add reference - Not working

    Is the required library definitely available on the machine the code fails on?
    If posting code please use code tags.

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Automatic add reference - Not working

    It would, IMO, be far better to late bind the code.

  4. #4
    Board Regular
    Join Date
    Aug 2012
    Posts
    54

    Default Re: Automatic add reference - Not working

    Quote Originally Posted by Norie View Post
    Is the required library definitely available on the machine the code fails on?
    Yes, the library is available. One of the ways I checked this is by deleting the code and manually selecting the library on the failing computer.

  5. #5
    Board Regular
    Join Date
    Aug 2012
    Posts
    54

    Default Re: Automatic add reference - Not working

    Quote Originally Posted by RoryA View Post
    It would, IMO, be far better to late bind the code.
    I tried late binding but didn't have any luck (my coding skill is pretty low). Can you provide code/format for code to conduct a late bind?

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Automatic add reference - Not working


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
  •  


DMCA.com