Use VBA to install Excel Reference

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:
And just in case anyone is looking here is the code for the following files:
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

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 Office\Office\EXCEL9.OLB"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\stdole2.tlb"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office\MSO9.DLL"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\FM20.DLL"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office\MSACC9.OLB"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office12\MSWORD.OLB"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\scrrun.dll"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\PDFCreator\PDFCreator.exe"
End Sub
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I see the problem, duplicate declarations, I have removed the stuff that you don't need in the script and it seem's to work OK, let me know how it goes.

Code:
Sub AddRuntimeLibrary()
    Const imgFileName = "PrintIcon"
  On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL"
    Const GUIDRef = "{000204EF-0000-0000-C000-000000000046}"
    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)
    On Error Resume Next  'Ignore Error If Reference Already Established
 
    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 4, 0
On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office\EXCEL9.OLB"
    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)
    On Error Resume Next  'Ignore Error If Reference Already Established
 
    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 1, 3
  On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\stdole2.tlb"
    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)
    On Error Resume Next  'Ignore Error If Reference Already Established
    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 2, 0
On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office\MSO9.DLL"
    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)
    On Error Resume Next  'Ignore Error If Reference Already Established
    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 2, 1
    On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\FM20.DLL"
    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)
    On Error Resume Next  'Ignore Error If Reference Already Established
    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 2, 0
On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office\MSACC9.OLB"
    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)
    On Error Resume Next  'Ignore Error If Reference Already Established
    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 9, 0
  On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)
    On Error Resume Next  'Ignore Error If Reference Already Established
    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 5, 0
On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office12\MSWORD.OLB"
    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)
    On Error Resume Next  'Ignore Error If Reference Already Established
    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 8, 4
        On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\scrrun.dll"
    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)
    On Error Resume Next  'Ignore Error If Reference Already Established
    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 1, 0
    On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\PDFCreator\PDFCreator.exe"
    Set PrntIcon = Application.CommandBars.FindControl(ID:=4)
    On Error Resume Next  'Ignore Error If Reference Already Established
    ThisWorkbook.VBProject.References.AddFromGuid GUIDRef, 7, 1
End Sub
 
Upvote 0
You may want to add the extensibility 5.3 back in as well as you need it for runing a lot of scripts.
 
Upvote 0
Of course the tweaks you made to the code now compile with no issues. Thanks again :)

What is the extensibility 5.3? I have never used that before? What features does that add?

What is the difference between the code of the Add_Ref and the AddRunTimeLibrary Modules? Or do they do the same thing?
 
Last edited:
Upvote 0
The Add_Ref and Addruntime are just the names of your script, so if you were looking for it you would know what it is and you could call it using other macros.

If you manually record a macro it will be macro1 the next record will be macro 2 etc... it is best to use names that you remember so that if you need to find it in the future you can, my is addruntimelibrary as that is the name I know to search for as the macro adds the runtime reference.

So it doesn't matter what you name it.

In regards to the 5.3, as stated it is a very important reference that you will require if you start getting more involved with VBA script, many macros will not work unless it is installed, you can google it if you want to know more about it.

Glad it worked.
 
Upvote 0
So essentially I would want to put each reference in its own module so that when trying to call them, the names are easily remembered?

I will google it, and see what it says. Heck, I'll probably add the reference anyway, and see what all if any differences I can tell, then google.
 
Upvote 0
You can just call it Add_refs or Add_References as you know what you would be looking for as all of them are references, you don't have to put them in separate modules as it is less script to have them all in the one script.
 
Upvote 0
I know I am a few days late on this thread, but I do not see the Extensibilty 5.3 as an avaliable reference in my Excel 2000???


Disregard it is Microsoft Visual Basic for Applications Extensibility 5.3 -- Now try saying that 5 times really fast!!
 
Upvote 0

Forum statistics

Threads
1,224,837
Messages
6,181,255
Members
453,028
Latest member
letswriteafairytale

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