you've crreated a reference in you workbook to either a file that Paul does
not have or the file is located in a different path than yours is.
Open the 'problem' workbook. From that workbook run the program below
[ListActiveVBEReferences] to get a list of all the active references you have
in your workbook.
Adjust the 2nd program below [AddVbideReferencesFromGUID] to include your
NAME and GUID for each of the references shown when you ran
ListActiveVBEReferences.
Add the adjusted program to the on open procedure of your ThisWorkbook of
your 'problem' workbook so that whenever it opens, the references will be
activated.
--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select
''''''''''''''''YES'''''''''''''''' at the bottom of the post.
'==========================================
Public Sub ListActiveVBEReferences()
'On Error GoTo Err_ListActiveVBEReferences
Dim aryHiddensheets()
Dim refReference
Dim i As Integer, x As Integer
Dim iWorksheets As Integer, y As Integer
Dim strResultsTableName As String
strResultsTableName = "Active VBE References"
'check for an active workbook
If ActiveWorkbook Is Nothing Then 'no workbooks open, so create one
Workbooks.Add
End If
'Count number of worksheets in workbook
iWorksheets = ActiveWorkbook.Sheets.Count
'redim array
ReDim aryHiddensheets(1 To iWorksheets)
'put hidden sheets in an array, then unhide the sheets
For x = 1 To iWorksheets
If Worksheets(x).Visible = False Then
aryHiddensheets(x) = Worksheets(x).name
Worksheets(x).Visible = True
End If
Next
'Check for duplicate Worksheet name
i = ActiveWorkbook.Sheets.Count
For x = 1 To i
If Windows.Count = 0 Then Exit Sub
If UCase(Worksheets(x).name) = _
UCase(strResultsTableName) Then
Worksheets(x).Activate
If Err.Number = 9 Then
Exit For
End If
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Exit For
End If
Next
'Add new worksheet at end of workbook
' where results will be located
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
'Name the new worksheet and set up Titles
ActiveWorkbook.ActiveSheet.name = strResultsTableName
ActiveWorkbook.ActiveSheet.Range("A1").value = "Description"
ActiveWorkbook.ActiveSheet.Range("B1").value = "Name"
ActiveWorkbook.ActiveSheet.Range("C1").value = "GUID"
ActiveWorkbook.ActiveSheet.Range("D1").value = "#Major"
ActiveWorkbook.ActiveSheet.Range("E1").value = "#Minor"
ActiveWorkbook.ActiveSheet.Range("F1").value = "Path"
ActiveCell.Offset(1, 0).Select
For Each refReference In _
Application.VBE.ActiveVBProject.references
With ActiveCell
.value = refReference.Description
.Offset(0, 1).value = refReference.name
.Offset(0, 2).value = refReference.GUID
.Offset(0, 3).value = refReference.Major
.Offset(0, 4).value = refReference.Minor
.Offset(0, 5).value = refReference.FullPath
.Offset(1, 0).Select
End With
Next
'format worksheet
ActiveWindow.Zoom = 75
Range("A1:F1").Select
Range("F1").Activate
Selection.Font.Bold = True
Range("A2").Select
ActiveWindow.FreezePanes = True
Range("F1").Activate
Columns("A:F").EntireColumn.AutoFit
On Error Resume Next
Range("A1:F1").Select
Range("F1").Activate
With Selection.Font
.name = "Tahoma"
.FontStyle = "Bold"
.Underline = xlUnderlineStyleSingleAccounting
End With
Columns("D:E").Select
Range("E1").Activate
With Selection
.HorizontalAlignment = xlCenter
End With
Columns("A:F").Select
Range("F1").Activate
Columns("A:F").EntireColumn.AutoFit
Columns("F:F").Select
If Selection.ColumnWidth > 50 Then
Selection.ColumnWidth = 50
End If
Columns("F:F").Select
With Selection
.WrapText = True
End With
Range("A2").Select
're-hide previously hidden sheets
On Error Resume Next
y = UBound(aryHiddensheets)
For x = 1 To y
Worksheets(aryHiddensheets(x)).Visible = False
Next
Application.Dialogs(xlDialogWorkbookName).Show
Exit_ListActiveVBEReferences:
Exit Sub
Err_ListActiveVBEReferences:
MsgBox "Error: " & Err & " - " & Err.Description
Resume Exit_ListActiveVBEReferences
End Sub
'======================================
Sub AddVbideReferencesFromGUID()
'Add references used in procedures
'
'
Dim aryReference() As String
Dim iErrorCounter As Long
Dim iReferences As Long, i As Long
Dim x As Long, y As Long
Dim iMajor As Long, iMinor As Long
Dim strADODB As String ' GUID reference
Dim strADOR As String ' GUID reference
Dim strADOX As String ' GUID reference
Dim strCDO As String ' GUID reference
' Dim strDSOleFile As String ' GUID reference
Dim strExcel As String ' GUID reference
Dim strIWshRuntimeLibrary As String ' GUID reference
Dim strMSACAL As String ' GUID reference
Dim strMSForms As String ' GUID reference
Dim strOffice As String ' GUID reference
Dim strOutlook As String ' GUID reference
Dim strRefEdit As String ' GUID reference
Dim strScripting As String ' GUID reference
Dim strstdole As String ' GUID reference
Dim strVBA As String ' GUID reference
Dim strVBIDE As String ' GUID reference
Dim VarAddReference As Variant
'MsgBox "GUID is: " & ThisWorkbook.VBProject.References("VBIDE").GUID
'/--------VARIABLES--------------------------/
iReferences = 15
iMajor = 20
iMinor = 20
'/-------------------------------------------/
On Error GoTo Err_AddVbideReferences
ReDim aryReference(iReferences, 2)
iErrorCounter = 0
' 'Microsoft ActiveX Data Objects 2.5 Library
aryReference(1, 1) = "ADODB"
aryReference(1, 2) = "{00000205-0000-0010-8000-00AA006D2EA4}"
' 'Microsoft ActiveX Data Objects Recordset 2.5 Library
aryReference(2, 1) = "ADOR"
aryReference(2, 2) = "{00000300-0000-0010-8000-00AA006D2EA4}"
'Microsoft ADO Ext. 2.5 for DDL and Security
aryReference(3, 1) = "ADOX"
aryReference(3, 2) = "{00000600-0000-0010-8000-00AA006D2EA4}"
'Microsoft CDO for Windows 2000 Library
aryReference(4, 1) = "CDO"
aryReference(4, 2) = "{CD000000-8B95-11D1-82DB-00C04FB1625D}"
'Microsoft Excel 9.0 Object Library
aryReference(5, 1) = "Excel"
aryReference(5, 2) = "{00020813-0000-0000-C000-000000000046}"
'Windows Script Host Object Model (Ver 1.0)
aryReference(6, 1) = "IWshRuntimeLibrary"
aryReference(6, 2) = "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B}"
'Microsoft Calendar Control 9.0
aryReference(7, 1) = "MSACAL"
aryReference(7, 2) = "{8E27C92E-1264-101C-8A2F-040224009C02}"
'Microsoft Forms 2.0 Object Library
aryReference(8, 1) = "MSForms"
aryReference(8, 2) = "{0D452EE1-E08F-101A-852E-02608C4D0BB4}"
'Microsoft Office 9.0 Object Library
aryReference(9, 1) = "Office"
aryReference(9, 2) = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"
'Microsoft Outlook 9.0 Object Library
aryReference(10, 1) = "Outlook"
aryReference(10, 2) = "{00062FFF-0000-0000-C000-000000000046}"
'Ref Edit Control
aryReference(11, 1) = "RefEdit"
aryReference(11, 2) = "{00024517-0000-0000-C000-000000000046}"
'Microsoft Scripting Runtime
aryReference(12, 1) = "Scripting"
aryReference(12, 2) = "{420B2830-E718-11CF-893D-00A0C9054228}"
'OLE Automation
aryReference(13, 1) = "stdole"
aryReference(13, 2) = "{00020430-0000-0000-C000-000000000046}"
'Visual Basic For Applications
aryReference(14, 1) = "VBA"
aryReference(14, 2) = "{000204EF-0000-0000-C000-000000000046}"
'Microsoft Visual Basic for Applications Extensibility 5.3
aryReference(15, 1) = "VBIDE"
aryReference(15, 2) = "{0002E157-0000-0000-C000-000000000046}"
'Debug.Print ActiveWorkbook.name
For i = 1 To iReferences
For x = iMajor To 0 Step -1
For y = iMinor To 0 Step -1
VarAddReference = _
ActiveWorkbook.VBProject.references.AddFromGuid(aryReference(i,
2), x, y)
Next y
Next x
Next i
Exit_AddVbideReferences:
Exit Sub
Err_AddVbideReferences:
iErrorCounter = iErrorCounter + 1
If Err = 32813 Then ' if Reference already active, ignore error and exit
iErrorCounter = 0
Resume Next
End If
If iErrorCounter > 4 Then
iErrorCounter = 0
Resume Next
End If
If Err = 438 Then ' Object doesn't support this property or method
' This error is often gotten first time thru an
' add reference routine
' AddVbideReferencesFromGUID
' Exit Sub
iErrorCounter = 0
Resume Next
End If
' MsgBox "Error: " & Err & " - " & Err.Description
iErrorCounter = 0
Resume Next
End Sub
'/==================================================/
