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:

jo15765

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

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Jaye7

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

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
You may want to add the extensibility 5.3 back in as well as you need it for runing a lot of scripts.
 

jo15765

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

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060

ADVERTISEMENT

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.
 

jo15765

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

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060

ADVERTISEMENT

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.
 

jo15765

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

Watch MrExcel Video

Forum statistics

Threads
1,127,519
Messages
5,625,278
Members
416,086
Latest member
CaptainGD

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