Excel VBA Project References

[simora]

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]

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]

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]

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]

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]

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]

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]

Thanks all.