tca_VB
02-12-2008, 07:01 AM
I have been using the information on KBs 267 and (search GUID) and am fairly close to a solution.
I've been able to mark VBA as Trusted and add a single reference library.
Question - can I expand on KB 267 and add more than one reference at a time by using the following (see red)?
Sub AddReference()
'Macro purpose: To add a reference to the project using the GUID for the
'reference library
Dim strGUID As String, theRef As Variant, i As Long, strGUIDb As String
'Update the GUID you need below.
strGUID = "{00020905-0000-0000-C000-000000000046}"
strGUIDb = "{00020813-0000-0000-C000-000000000046}"
'Set to continue in case of error
On Error Resume Next
'Remove any missing references
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i
'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear
'Add the reference
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUID, Major:=1, Minor:=0
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUIDb, Major:=1, Minor:=0
'If an error was encountered, inform the user
Select Case Err.Number
Case Is = 32813
'Reference already in use. No action necessary
Case Is = vbNullString
'Reference added without issue
Case Else
'An unknown error was encountered, so alert the user
MsgBox "A problem was encountered trying to" & vbNewLine _
& "add or remove a reference in this file" & vbNewLine & "Please check the " _
& "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
End Select
On Error Goto 0
End Sub
Once run on the MS Excel 2007, there was still a reference missing. On my XL 2002 SP3 I have Microsoft Calendar Control 10.0 This was missing on XL 2007. I finally reconciled manually by checking Microsoft Works Calendar 5.0 Type Library. Question - can this reference be added in the AddReference part of the code using GUID:
Reference Name: MSACAL
Full Path to Reference: C:\PROGRA~1\MICROS~3\Office10\MSCAL.OCX
Reference GUID: {8E27C92E-1264-101C-8A2F-040224009C02}?
If not, does anyone know what GUID?
Thanks for the help and past KBs and threads.
I've been able to mark VBA as Trusted and add a single reference library.
Question - can I expand on KB 267 and add more than one reference at a time by using the following (see red)?
Sub AddReference()
'Macro purpose: To add a reference to the project using the GUID for the
'reference library
Dim strGUID As String, theRef As Variant, i As Long, strGUIDb As String
'Update the GUID you need below.
strGUID = "{00020905-0000-0000-C000-000000000046}"
strGUIDb = "{00020813-0000-0000-C000-000000000046}"
'Set to continue in case of error
On Error Resume Next
'Remove any missing references
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i
'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear
'Add the reference
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUID, Major:=1, Minor:=0
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUIDb, Major:=1, Minor:=0
'If an error was encountered, inform the user
Select Case Err.Number
Case Is = 32813
'Reference already in use. No action necessary
Case Is = vbNullString
'Reference added without issue
Case Else
'An unknown error was encountered, so alert the user
MsgBox "A problem was encountered trying to" & vbNewLine _
& "add or remove a reference in this file" & vbNewLine & "Please check the " _
& "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
End Select
On Error Goto 0
End Sub
Once run on the MS Excel 2007, there was still a reference missing. On my XL 2002 SP3 I have Microsoft Calendar Control 10.0 This was missing on XL 2007. I finally reconciled manually by checking Microsoft Works Calendar 5.0 Type Library. Question - can this reference be added in the AddReference part of the code using GUID:
Reference Name: MSACAL
Full Path to Reference: C:\PROGRA~1\MICROS~3\Office10\MSCAL.OCX
Reference GUID: {8E27C92E-1264-101C-8A2F-040224009C02}?
If not, does anyone know what GUID?
Thanks for the help and past KBs and threads.