Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Setting a VBA Library Reference

  1. #1
    Join Date
    1st April 2003
    Location
    Tennessee
    Posts
    88

    Setting a VBA Library Reference

    Hi, all.

    I know that I've seen some code here that sets a VBA Library reference, but I didn't have a need for it then.
    As it turns out, now I do need this code.

    We are currently running W2k, and the system I've developed creates new user files based on a template.
    This template was created using wk2, so has the Excel 9 library reference.
    However, when using an XP machine to create a new file, it creates a reference to the Excel 10 library.
    Then, when using the newly created file on a W2K machine, there is a missing library error.

    We are currently rolling out XP to all our users, but this will take place over a period of about 6 months.
    Untill all users are on XP, we will need to force a reference to Excel 9, which is why I need the code.

    Thanks for your help.
    Steve

  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,507
    I think XL-Dennis posted that code. You could search his posts or U2U him, I am sure he will help.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    1st April 2003
    Location
    Tennessee
    Posts
    88
    I looked thru all the posts from xl-dennis of the last 3 months and didn't see anything.

    Anyway, I've got it partially solved.

    First, I added this code to the template, and run it from the Worksheet_Open event handler
    VB:
    Public Sub CheckReferences() 
        Dim i As Integer 
         
        For i = 1 To ThisWorkbook.VBProject.References.Count 
            If ThisWorkbook.VBProject.References(i).IsBroken Then 
                ThisWorkbook.VBProject.References.Remove  _ 
                ThisWorkbook.VBProject.References(i) 
                Exit For 
            End If 
        Next i 
         
        If Application.Version = "9.0" Then 
            ThisWorkbook.VBProject.References.AddFromFile _ 
            "C:\Program Files\Microsoft Office\Office\msoutl9.olb" 
        Else 
            ThisWorkbook.VBProject.References.AddFromFile  _ 
            "C:\Program Files\Microsoft Office\Office10\msoutl.olb" 
        End If 
    End Sub 
    
    
    I created the file using W2K, and then opened it using XP, and it worked fine. I saved it from XP, then opened it from W2K. I get an error on the reference remove statement, which says the reference is not registered. However, when the Add statement executes, the error message says 'reference name already in use'.

    Any ideas?
    Steve

  4. #4
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684
    Hi

    You might want to lok into the code lines:
    If Application.Version = "9.0" Then

    Also this will be :
    If Application.Version = "10.0" Then

    ‘ your codes for each procedure

    I recall code is different from XL2000 and ExcelXP

    Kind regards

    Jack

    BTW XL-Dennis is a very close friend of mine and he is a little busy right now on his Swedish web site, I expect him back in a week or so.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745
    Hi Steve,

    You need to use GUIDs. Have a look at the example workbook - you can list all of the references in the workbook using the routine "Grab_References2" and you can set references using the routine "CreateRef_Library_MicrosoftGraph8" (set up for the Graph 8 object library at the moment but easily amended to suit your requirements).

    Thanks to NateO for the first routine and Ivan for the second.

    HTH
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  6. #6
    Join Date
    1st April 2003
    Location
    Tennessee
    Posts
    88
    Jack,

    This is an internal company application, and we all have identical PCs with identical images on them. We know that if it is not version 9 (W2K) it will be XP.
    But thanks for the tip - if it were an app for use by folks with many different versions, I would have to code for that.

    Richie,
    When the reference on the file is to Outlook 9, and I open the file on XP, the Remove and the .AddFromFile (Outlook10) work fine.

    Once it is saved in XP and you attempt to open it with W2K, the .Remove statement fails, generating a 'Not Registered' error. Then when it gets to the .AddFromFile (Outlook 9) statement, another error occurs stating there is a reference name conflict.
    If I stop and manually remove the missing reference, then resume, the .AddFromFile statment (Outlook 9) works fine.

    It looks like there is no automated way to remove a missing reference.

    By the way, I'm developing using W2K.

    Thanks to both of you for your input.
    Steve

  7. #7
    Join Date
    1st April 2003
    Location
    Tennessee
    Posts
    88
    Here's what I decided to do on this:

    In the before_save event, remove the reference to Outlook.

    In the workbook_open event, add the reference as follows:

    if version = 9.0 add Outlook 9 reference, otherwise add Outlook 10 reference. This will be OK because I know the users have either version 9 (W2K) or version 10 (XP).

    However, there is a new setting in Excel XP on the Macros, Security, Trusted Sources page that doesn't exist in Excel 2000: Trust access to Visual Basic Project. By default (at least on our image) it is unchecked, so the code to add a reference is not allowed. If I check the box, the code above works fine.

    So, the question now is how can I set this checkbox with code so that i don't have to require the users to manually check this box? Any ideas?
    Steve

  8. #8
    Join Date
    25th January 2003
    Location
    Östersund, Sweden
    Posts
    2,451
    Hi all,

    I got an U2U-message from brewguy99 about this thread so here I am :wink1:

    There are two different approaches we can use to establish reference to an external object library, either early binding or late binding.

    The early binding means that we set a reference via the Tools | Reference in the VB-ediitor. This approach works well if we only have to deal with one version of an object library, like Outlook 9.0 or Outlook 10.

    However, by using late binding we create, via code, a reference to the present library no matter which version is in use.

    You may read more about it here:
    INFO: Using Early Binding and Late Binding in Automation

    the drawback with late binding is that it is executed slower compared the early binding however it provide at least a solution

    What I can understand from Your first mail is that the template in use is regular saved and since You have both 2K and the XP-version of XL You get an error due to version-conflict of Outlooks library.

    Below You find the late binding approach:

    Option Explicit

    Sub Late_Binding()
    Dim olObj As Object
    Set olObj = CreateObject("Outlook.Application")

    'Your code here

    Set olObj = Nothing
    End Sub

    Please mail Your present code for working with Outlook objects in order to give You more assistant.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  9. #9
    Join Date
    1st April 2003
    Location
    Tennessee
    Posts
    88
    Dennis

    Thank you for your wisdom. By changing all my object references to late binding I was able to totally remove all the added VB Library references.

    As a note to anyone else, I was using early binding, and used some named constants (olFolderInbox, olMailItem), and with late binding these constants are no longer defined. I just looked at the Outlook Object browser to find out their values, and then defined them as constants within the sub.

    An added bonus is that the library no longer has to be embedded in the file, which reduces the file size considerably.

    I also no longer need to include the reference to the VBA Extension library.

    Thanks again,
    Steve

  10. #10
    Join Date
    25th January 2003
    Location
    Östersund, Sweden
    Posts
    2,451
    You´re welcome and I´m glad it solved the issue

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Set Library Reference for Word
    By Farrah Farley in forum EXCEL HELP
    Replies: 21
    Last Post: November 11th, 2005, 18:38
  2. Autoload reference library by name
    By breakingstein in forum EXCEL HELP
    Replies: 18
    Last Post: September 1st, 2005, 11:37
  3. Reference Library for Formulas?
    By socha in forum EXCEL HELP
    Replies: 1
    Last Post: June 2nd, 2005, 09:13
  4. reference library
    By socha in forum EXCEL HELP
    Replies: 8
    Last Post: December 16th, 2003, 09:13
  5. VBA: reference library?
    By Jason_Moffitt in forum EXCEL HELP
    Replies: 2
    Last Post: November 13th, 2003, 06:03

Bookmarks

Posting Permissions

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