PDA

View Full Version : [SOLVED] Excel VBA Project References



simora
05-15-2017, 06:34 PM
I have a sheet with all the Project references that I want installed on the user's system to enable Excel Macros run correctly.
I need to go through the list on the GUID worksheet , Column C, and see if its already installed on the Users system.
If its not installed, I want to install it.

If an error occurs, I want to have the error written to the worksheet in Column H
See attached workbook
Using Office 2007

Paul_Hossler
05-15-2017, 08:08 PM
Try this




Option Explicit

Sub CheckReferences()
Dim i As Long, j As Long
Dim rGuids As Range

Set rGuids = Worksheets("GUID").Cells(1, 1).CurrentRegion.Columns(4)

For i = 2 To rGuids.Rows.Count
For j = 1 To ThisWorkbook.VBProject.References.Count
If ThisWorkbook.VBProject.References(j).GUID = rGuids.Cells(i, 1).Value Then
GoTo NextGUID
End If
Next j
Worksheets("GUID").Cells(i, 8).Value = "Missing"
NextGUID:
Next I

End Sub

simora
05-15-2017, 10:07 PM
Hi Thanks:

I modified the code to ADD the missing References like this;



Worksheets("GUID").Cells(i, 8).Value = "Missing"
Gref = Worksheets("GUID").Cells(i, 4).Value

ActiveWorkbook.VBProject.References.AddFromGuid Gref, Worksheets("GUID").Cells(i, 5).Value, Worksheets("GUID").Cells(i, 6).Value

NextGUID:


What I can't figure out is why is
Microsoft VBScript_Regular Expressions 5.5 not being installed.
All the other References are OK.

snb
05-15-2017, 11:43 PM
Sub M_snb()
sn = sheets("GUID").Cells(1, 1).CurrentRegion
For Each it In ThisWorkbook.VBProject.References
c00 = c00 & " " & it.GUID
Next

For j = 2 To ubound(sn)
If instr(c00,sn(j,4))= 0 then thisWorkbook.VBProject.References.AddFromGuid sn(j,4), sn(j,5), sn(j,6)
Next
End Sub

When you use VBA you should avoid any interaction with a worksheet as much as possible.

simora
05-16-2017, 01:12 AM
Thanks snb (http://www.vbaexpress.com/forum/member.php?44644-snb):


Good information.
However, What I'm trying to figure out is why
Microsoft VBScript_Regular Expressions 5.5 is not being installed.

Your code did not execute. It gave an error at


ThisWorkbook.VBProject.References.AddFromGuid it, sn(j, 5), sn(j, 6)

it = Empty

Didn't fully understand the detailed logic of your code. I couldn't see exactly where its determining if a VBProject.Reference is already installed.

snb
05-16-2017, 02:05 AM
It should be:

thisWorkbook.VBProject.References.AddFromGuid sn(j,4), sn(j,5), sn(j,6)


Both Librareis have the same GUID.
Probably VBA finds it hard to choose which one to load.

Paul_Hossler
05-16-2017, 06:26 AM
Hi Thanks: What I can't figure out is why is Microsoft VBScript_Regular Expressions 5.5 not being installed. All the other References are OK.



I've always used AddFromFile



Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files (X86)\Microsoft Office\Office14\MSPPT.OLB"

simora
05-16-2017, 08:00 PM
Thanks all.