Use VBA to install Excel Reference

[jo15765]

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:

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

 

[jo15765]

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

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

 

[Jaye7]

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.

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]

You may want to add the extensibility 5.3 back in as well as you need it for runing a lot of scripts.

 

[jo15765]

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?

 

[Jaye7]

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]

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]

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]

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!!

 

[Milu]

If i don't know path then?