Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA References (XP; SP3)

    Is there a way to control the VBA references via code?

    I would like to check/uncheck the reference to Acrobat Distiller.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA References (XP; SP3)

    You must set a reference to be able to manipulate references in code. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library.
    This makes (among other things) the References collection available in VBA.

    To add a reference, you can use either AddFromFile, for example:

    ActiveWorkbook.VBProject.References.AddFromFile "C:Program FilesCommon FilesMicrosoft SharedOffice10mso.dll"

    or AddFromGuid:

    ActiveWorkbook.VBProject.References.AddFromGuid "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"

    You can find the Guid for references that have been set as follows:

    Sub ListReferences()
    Dim ref As Reference
    For Each ref In ThisWorkbook.VBProject.References
    Debug.Print ref.Name, ref.GUID, ref.FullPath
    Next ref
    Set ref = Nothing
    End Sub

    You can also look them up in the Windows Registry.

    To remove a reference, use code like this:

    With ActiveWorkbook.VBProject.References
    .Remove .Item("Office")
    End With

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA References (XP; SP3)

    Wriggly Stockings,

    Thank you very much.

    John

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA References (XP; SP3)

    Hans,

    I set a reference to MVBA Extensibility 5.3 Library and I get the following error message using:

    With Active Workbook.VBProject.References
    .Remove .Item("Acrobat Distiller")
    End With


    John

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA References (XP; SP3)

    ActiveWorkbook is one word.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA References (XP; SP3)

    Hans,

    I have it as one word. I typed it incorrectly into this thread.

    John

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA References (XP; SP3)

    In Excel, select Tools | Macro | Security.
    Activate the Trusted Sources tab.
    Tick the check box "Trust access to VB project".
    Click OK.

    If that doesn't help, I'm stumped. The code works correctly on my PC.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA References (XP; SP3)

    Hans,

    Your suggestion did work. However I now am faced with a different challenge or message. Trying to continue or not getting the message.

    Using this code:
    Sub RemoveDistiller()
    With ThisWorkbook.VBProject.References
    .Remove .Item("ACRODISTXLib")
    End With
    End Sub


    By the way my reference name was incorrect. I was trying to delete or uncheck "Acrobat Distiller" while the reference name is "ACRONDISTXLib"

    Try the following code to grab the reference names and etc.:

    Sub Grab_References()
    Dim n As Integer

    Sheets.Add
    ActiveSheet.Name = "GUIDS"

    On Error Resume Next
    For n = 1 To ActiveWorkbook.VBProject.References.Count
    Cells(n, 1) = ActiveWorkbook.VBProject.References.Item(n).Name
    Cells(n, 2) = ActiveWorkbook.VBProject.References.Item(n).Descri ption
    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).FullPa th
    Next n

    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA References (XP; SP3)

    Have you set a breakpoint in your code, or are you trying to single-step through it? Neither is possible when you're manipulating the VB Project in code.

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA References (XP; SP3)

    Hans,

    I was trying to single-step through it and it was giving me the message.

    If I let it run it works.

    Thanks,
    John

Posting Permissions

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