PDA

View Full Version : GUID for Excel 2007



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.

tca_VB
02-12-2008, 10:21 AM
Can someone with Excel 2007 provide me with the GUID for Microsoft Works Calendar 5.0 Type Library? (I have Excel 2002 SP3, and I can't find this information online. This will help with part one of my question.)

The method I used to determine the GUID is the following:
Open VBAProject (Alt-11) and in Tools>References check the Microsoft Works Calendar 5.0 Type Library.

Then run the following code supplied by a KB at this site. (I put into a module and then ran it from a blank worksheet)



Option Explicit

Sub ListReferencePaths()
'Macro purpose: To determine full path and Globally Unique Identifier (GUID)
'to each referenced library. Select the reference in the Tools\References
'window, then run this code to get the information on the reference's library

On Error Resume Next
Dim i As Long
With ThisWorkbook.Sheets(1)
.Cells.Clear
.Range("A1") = "Reference name"
.Range("B1") = "Full path to reference"
.Range("C1") = "Reference GUID"
End With
For i = 1 To ThisWorkbook.VBProject.References.Count
With ThisWorkbook.VBProject.References(i)
ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0) = .Name
ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 1) = .FullPath
ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 2) = .GUID
End With
Next i
On Error GoTo 0
End Sub