Use VBA to install Excel Reference

jo15765

jo15765

Well-known Member
Joined
Sep 23, 2011
Messages
591
I was wondering if there was a way to use the Workbook_Open() event in Excel to install specific references. I tried to record a macro, and then open the VBA editor and then see what code it showed to install, but there was no code in the macro :(

Can anyone shed some insight on how to perform this, or if this is even possible.

I tried:
Code: 
With Workbook.reference
.AddFromFile "C:\WINDOWS\sytem32.stdole2.tlb"
End With

Which is just giving the location of the reference I want to install, and It gives a debug error of Run Time Error 424 Object Required
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
If you provide me with the reference names that you want i.e scriptingruntime or powerpoint reference etc... and the version of excel that you are using I can provide you with a script similar to the following.


Code: 
Public Sub AddPPT()
On Error Resume Next
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files (X86)\Microsoft Office\Office14\MSPPT.OLB"
 
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft OfficeXP\Office10\MSPPT.OLB"
End Sub
 
Upvote 0
There are a couple....Thank you for the assistance:
Visual Basic For Applications
Microsoft Excel 9.0 Object Library
OLE Automation
Microsoft Office 9.0 Object Library
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Word 12.0 Object Library

And this is Excel 2000.
 
Upvote 0
I don't have some of the references in my library, however I can provide some and the guide to retrieving the others.

This is the procedure for adding what I could

Code: 
Sub Add_Refs()
On Error Resume Next
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft OfficeXP\Office10\EXCEL.EXE"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\stdole2.tlb"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\Office10\MSO.DLL"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft OfficeXP\Office10\MSWORD.OLB"
End Sub
To add the others copy the following script into a module.
Go into the VBA references and make sure the ones that you want are ticked and then run the following.
Once it is run it will list them on a spreadsheet.
From here, just copy one of the application.vbe scripts and replace the C:\ code with the new C:\ file address location.

I hope that makes sense.


Code: 
Sub ListGUID()
     Dim Ref As Object, N&
     Sheets.Add
     Application.ScreenUpdating = False
     Cells.Font.Size = 8
     With Rows("1:1")
           .Font.Size = 9
           .Font.Bold = True
           .Font.ColorIndex = 9
           .Font.Underline = xlUnderlineStyleSingle
     End With
     [A1:D1] = _
     Array("Description", "Name", "Use: ThisWorkbook.VBProject.References.AddFromGuid", "Path")
     For Each Ref In ActiveWorkbook.VBProject.References
           [A65536].End(xlUp).Offset(1, 0) = Ref.Description
           [B65536].End(xlUp).Offset(1, 0) = Ref.Name
           [C65536].End(xlUp).Offset(1, 0) = """" & Ref.GUID & """" & ", " & Ref.Major & ", " & Ref.minor
           [D65536].End(xlUp).Offset(1, 0) = Ref.FullPath
     Next
     Columns("A:D").EntireColumn.AutoFit
     Set Ref = Nothing
End Sub
 
Upvote 0
So all I really need is the path that displays at the bottom of the references window, when I click on the specific reference?

Also, is there a way to check if the reference is already installed, and if it is, do nothing, if it is not, install?
 
Upvote 0
You can modify the following, it is what I use in my addin for making sure that the scripting runtime is installed.

If you post it into your module the green lines will advise what needs to be changed.

Code: 
Sub AddRuntimeLibrary()
  On Error Resume Next
 
  'change this to your reference file C:\ ???
 
  Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\scrrun.dll"
 
Const imgFileName = "PrintIcon"
 
'change the following to the GUID number that is shown on your sheet
 
    Const GUIDRef = "{0002E157-0000-0000-C000-000000000046}"  'MS VBA EXTENSIBILITY 5.3 GUID
 
    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)
 
    On Error Resume Next  'Ignore Error If Reference Already Established
 
    'change the following 5, 3 to the GUID number that is shown on your sheet
 
    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 5, 3
 
On Error Resume Next
 
 'change this to your reference file C:\ ???
 
Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\scrrun.dll"
End Sub
 
Upvote 0
Just to make sure I understand...If I add this code:

Code: 
Sub AddRuntimeLibrary()   On Error Resume Next     'change this to your reference file C:\ ???     Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\scrrun.dll"   Const imgFileName = "PrintIcon"   'change the following to the GUID number that is shown on your sheet       Const GUIDRef = "{0002E157-0000-0000-C000-000000000046}"  'MS VBA EXTENSIBILITY 5.3 GUID       Set PrntIcon = Application.CommandBars.FindControl(ID:=4)       On Error Resume Next  'Ignore Error If Reference Already Established       'change the following 5, 3 to the GUID number that is shown on your sheet       ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 5, 3   On Error Resume Next    'change this to your reference file C:\ ???   Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\scrrun.dll" End Sub
[\code]

To my workbook_open() event it will cycle through all of the references listed above and if they are not installed, install them, and if they are jump to the next, is that correct?

I am also getting a syntax error on all of the lines that start with: Const GUIDRef
 
Last edited:
Upvote 0
Sorry I can't help you with the error as I have tested the script on 2 versions of excel with all possible references removed and it works fine on both.
 
Upvote 0
Did I possibly set up the code wrong? Here is what I am using:
Code: 
Sub AddRuntimeLibrary()
    Const imgFileName = "PrintIcon"

  On Error Resume Next

  'change this to your reference file C:\ ???

    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL"

'change the following to the GUID number that is shown on your sheet

    Const GUIDRef = "{000204EF-0000-0000-C000-000000000046}"

    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)

    On Error Resume Next  'Ignore Error If Reference Already Established

    'change the following 5, 3 to the GUID number that is shown on your sheet

    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 4, 0

On Error Resume Next

 'change this to your reference file C:\ ???

    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office\EXCEL9.OLB"

'change the following to the GUID number that is shown on your sheet

    Const GUIDRef = "{00020813-0000-0000-C000-000000000046}"

    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)

    On Error Resume Next  'Ignore Error If Reference Already Established

    'change the following 5, 3 to the GUID number that is shown on your sheet

    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 1, 3

  On Error Resume Next

  'change this to your reference file C:\ ???

    Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\stdole2.tlb"

Const imgFileName = "PrintIcon"

'change the following to the GUID number that is shown on your sheet

    Const GUIDRef = "{00020430-0000-0000-C000-000000000046}"

    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)

    On Error Resume Next  'Ignore Error If Reference Already Established

    'change the following 5, 3 to the GUID number that is shown on your sheet

    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 2, 0

On Error Resume Next

 'change this to your reference file C:\ ???

    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office\MSO9.DLL"

    Const imgFileName = "PrintIcon"

'change the following to the GUID number that is shown on your sheet

    Const GUIDRef = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"

    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)

    On Error Resume Next  'Ignore Error If Reference Already Established

    'change the following 5, 3 to the GUID number that is shown on your sheet

    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 2, 1

    On Error Resume Next

  'change this to your reference file C:\ ???

    Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\FM20.DLL"

Const imgFileName = "PrintIcon"

'change the following to the GUID number that is shown on your sheet

    Const GUIDRef = "{0D452EE1-E08F-101A-852E-02608C4D0BB4}"

    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)

    On Error Resume Next  'Ignore Error If Reference Already Established

    'change the following 5, 3 to the GUID number that is shown on your sheet

    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 2, 0

On Error Resume Next

 'change this to your reference file C:\ ???

    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office\MSACC9.OLB"

    Const imgFileName = "PrintIcon"

'change the following to the GUID number that is shown on your sheet

    Const GUIDRef = "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}"

    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)

    On Error Resume Next  'Ignore Error If Reference Already Established

    'change the following 5, 3 to the GUID number that is shown on your sheet

    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 9, 0

  On Error Resume Next

   'change this to your reference file C:\ ???

    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"

Const imgFileName = "PrintIcon"

'change the following to the GUID number that is shown on your sheet

    Const GUIDRef = "{00025E01-0000-0000-C000-000000000046}"

    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)

    On Error Resume Next  'Ignore Error If Reference Already Established

    'change the following 5, 3 to the GUID number that is shown on your sheet

    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 5, 0

On Error Resume Next

 'change this to your reference file C:\ ???

    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office12\MSWORD.OLB"

    Const imgFileName = "PrintIcon"

'change the following to the GUID number that is shown on your sheet

    Const GUIDRef = "{00020905-0000-0000-C000-000000000046}"

    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)

    On Error Resume Next  'Ignore Error If Reference Already Established

    'change the following 5, 3 to the GUID number that is shown on your sheet

    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 8, 4

        On Error Resume Next

 'change this to your reference file C:\ ???

    Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\scrrun.dll"

    Const imgFileName = "PrintIcon"

'change the following to the GUID number that is shown on your sheet

    Const GUIDRef = "{420B2830-E718-11CF-893D-00A0C9054228}"

    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)

    On Error Resume Next  'Ignore Error If Reference Already Established

    'change the following 5, 3 to the GUID number that is shown on your sheet

    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 1, 0

    On Error Resume Next

 'change this to your reference file C:\ ???

    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\PDFCreator\PDFCreator.exe"

    Const imgFileName = "PrintIcon"

'change the following to the GUID number that is shown on your sheet

    Const GUIDRef = "{1CE9DC08-9FBC-45C6-8A7C-4FE1E208A613}"

    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)

    On Error Resume Next  'Ignore Error If Reference Already Established

    'change the following 5, 3 to the GUID number that is shown on your sheet

    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 7, 1
And on the GUIDRef it says Duplicate Decleration in Current Scope.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Mahantesh_Torgal
  • Question Question
Please help: VBA
Replies
2
Views
89
Mahantesh_Torgal
Mahantesh_Torgal
A
  • Solved
Manipulating Webpage Actions With VBA (via Selenium & Firefox Geckodriver)
2
Replies
12
Views
215
Ark68
A
R
  • Locked
  • Question Question
StockHistory no longer working
Replies
1
Views
806
Fluff
Fluff
J
  • Question Question
Excel Compiled VBA Corruption - Why Does It Happen?
Replies
2
Views
608
Fluff
Fluff
G
  • Question Question
Advice for Excel integration
Replies
1
Views
306
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,224,847
Messages
6,181,314
Members
453,032
Latest member
Pauh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top