Use VBA to install Excel Reference

jo15765

Well-known Member
Joined
Sep 23, 2011
Messages
570
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:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
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
 

jo15765

Well-known Member
Joined
Sep 23, 2011
Messages
570
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.
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
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
 

jo15765

Well-known Member
Joined
Sep 23, 2011
Messages
570

ADVERTISEMENT

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?
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
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
 

jo15765

Well-known Member
Joined
Sep 23, 2011
Messages
570

ADVERTISEMENT

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:

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
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.
 

jo15765

Well-known Member
Joined
Sep 23, 2011
Messages
570
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,203
Messages
5,623,350
Members
415,969
Latest member
Rey99

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
Top