Hello,
I have a workbook that adds an automatic reference to an external database (Microsoft ADODB Version 6.1).
The problem is that when I attempt to run the macro on another computer, it fails. Unfortunately, I don't get an error message and Excel crashes.
I noticed that if I delete the code below (that sets up the reference) and manually set up the external reference, the macro runs as intended. Any idea what is causing Excel to crash?
I have a workbook that adds an automatic reference to an external database (Microsoft ADODB Version 6.1).
The problem is that when I attempt to run the macro on another computer, it fails. Unfortunately, I don't get an error message and Excel crashes.
I noticed that if I delete the code below (that sets up the reference) and manually set up the external reference, the macro runs as intended. Any idea what is causing Excel to crash?
Code:
'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
'Update the GUID you need below.
strGUID = "{B691E011-1797-432E-907A-4D8C69339129}"
'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
'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