Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Unanswered: COM References in Code

    Hi guys


    is there anyway to reference COM Refrence (Tools _ References) in the code rather than through the menu's more specificaly i would like the refrences for

    Microsoft ActiveX Data Objects 2.6 Library
    Microsoft ADO ext 2.6 for DDL and Security
    Microsoft DAO ext 3.6 Object Library

    Any help with this would be hugely appreciated

    Dave

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Hi, Dave. Are you saying that you have already checked these references through Tools > References? If so, then you can reference them in code. Otherwise, no.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi shades what im trying to do (Told to by my boss acyually)
    is to refrence these objects in code rather than the refrences tool box,

    like in C you can refrence the specific dll's that your going to use

    i.e. if your reference is need is Microsoft ActiveX Data Objects 2.6 Library and the associated dll is something like mado26l.dll then use something like this

    set application.COMAddins.add mado26l.dll

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Not sure that can be done in VBA. You might want to check with the experts on this:

    http://www.vbaexpress.com/forum/
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    Pretty sure this can be done using the CreateObject function. After applying a reference using the references menu it should stay attached to the xls/xla file, so programatically adding the reference shouldn't be necessary. You would have a problem if a referenced dll was not installed on the clients machine.

    This example code from
    http://www.oreilly.com/catalog/proga...pter/ch02.html
    applies to access but function also works in Excel.
    Code:
     'Get path to Access database directory from Access SysCmd function.
        Set objAccess = CreateObject("Access.Application")
        strAccessDir = objAccess.SysCmd(9)
        strDBName = strAccessDir & "Samples\Northwind.mdb"
        Debug.Print "DBName: " & strDBName
        objAccess.Quit
     
        'Set up reference to Access database.
        Set dao = CreateObject("DAO.DBEngine.35")

    .
    ~

    Bill

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Shades and Bill

    Thanks Heaps for your help after some more reasearch i found a reference on OzGrid
    Auto Loading Vb Reference Libraries

    i modified the code slightly on the addition of references so ill post that here,
    If you can think of any improvements ill be more than willing to try

    Code:
    'Code Modified from OZ Grid
    ' http://www.ozgrid.com/forum/archive/index.php/t-22483.html
    
    Private Sub Workbook_Open()
    
    Dim refAdd
    Dim RefId(3) As String, MajorId(3) As Integer, MinorId(3) As Integer
    
        On Error GoTo ErrorHandler
        'Reference Object Library using Major / Minor GUID
        
        'Microsoft ActiveX Data Objects 2.6 Library
        RefId(1) = "{00000206-0000-0010-8000-00AA006D2EA4}": MajorId(1) = 2: MinorId(1) = 6
        'Microsoft DAO 3.6 Object Library
        RefId(2) = "{00025E01-0000-0000-C000-000000000046}": MajorId(2) = 5: MinorId(2) = 0
        'Microsoft ADO Ext. 2.6 for DDL and Security
        RefId(3) = "{00000600-0000-0010-8000-00AA006D2EA4}": MajorId(3) = 2: MinorId(3) = 6
        
        
        'add the references if they arn't there already
        For I = 1 To 3
            Set refAdd = ThisWorkbook.VBProject.References
            refAdd.AddFromGuid RefId(I), MajorId(I), MinorId(I)
        Next I
        
        
        Exit Sub
        
    ErrorHandler:
        'Check the State of the Error number and respond acordingly
        Select Case Err.Number
            Case 32813
                'Module Already open and Exsits ignore and continue
                Err.Number = 0
                Resume Next
            Case -2147319779
                'The Object Library not Registered
                MsgBox "Object Library Not Registered," & Chr(10) & _
                            "Macro Unusable with Current Version of Excel"
                Exit Sub
            Case Else
                MsgBox "Unhandled Error" & Chr(10) & _
                    Err.Number & " " & Err.Description & Chr(10) & _
                    "Please Take a note of this error and Contact David"
        End Select
    End Sub
    cheers

    Dave

Posting Permissions

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