A
Alex St-Pierre
Hi !
I would like to add Word References in Excel VBA. It works well almost all
the time except when MISSING: WORD LIBRARY is displayed in my reference. So,
what I do is to removes the Word Reference and re-add it. Alse, I have 2
errors that happens even when it doesn't have MISSING library. Any idea ?
Thanks.
Sub Macro_MailMerge_Excel()
RemoveOfficeRef ThisWorkbook, "Word"
AddOfficeRef ThisWorkbook, "WORD"
Macro_exec
End Sub
Option Explicit
Function AddOfficeRef(wb As Workbook, sApp As String)
Dim ref As Object
Dim refs As Object
Dim sID As String
On Error Resume Next
Set refs = wb.VBProject.References
If refs Is Nothing Then
MsgBox "need to change security settings"
Exit Function
End If
On Error GoTo 0
' sApp = UCase(sApp)
Select Case sApp
Case "WORD"
sID = "{00020905-0000-0000-C000-000000000046}"
Case "OUTLOOK"
sID = "{00062FFF-0000-0000-C000-000000000046}"
Case "ACCESS"
sID = "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}"
Case "EXCEL"
sID = "{00020813-0000-0000-C000-000000000046}"
Case Else
MsgBox sApp & " not known"
End Select
If Len(sID) = 0 Then Exit Function
For Each ref In refs
If ref.GUID = sID Then
Exit Function
End If
Next
On Error GoTo errH
refs.AddFromGuid sID, 0, 0 'Makes error here often?
Exit Function
errH:
MsgBox "Error setting ref to " & sApp
End Function
Function RemoveOfficeRef(wb As Workbook, strReference As String)
Dim ref2 As Object
Dim refs2 As Object
Dim c As String
On Error Resume Next
Set refs2 = wb.VBProject.References
If refs2 Is Nothing Then
MsgBox "need to change security settings"
Exit Function
End If
On Error GoTo 0
For Each ref2 In refs2
Err.Number = 0
On Error Resume Next 'Doesn't work with MISSING:WORD
If ref2.Name = strReference Then
wb.VBProject.References.Remove ref2 'Makes error here often?
Exit Function
End If
On Error GoTo 0
If Err.Number = 1 Then wb.VBProject.References.Remove ref2
Next
End Function
I would like to add Word References in Excel VBA. It works well almost all
the time except when MISSING: WORD LIBRARY is displayed in my reference. So,
what I do is to removes the Word Reference and re-add it. Alse, I have 2
errors that happens even when it doesn't have MISSING library. Any idea ?
Thanks.
Sub Macro_MailMerge_Excel()
RemoveOfficeRef ThisWorkbook, "Word"
AddOfficeRef ThisWorkbook, "WORD"
Macro_exec
End Sub
Option Explicit
Function AddOfficeRef(wb As Workbook, sApp As String)
Dim ref As Object
Dim refs As Object
Dim sID As String
On Error Resume Next
Set refs = wb.VBProject.References
If refs Is Nothing Then
MsgBox "need to change security settings"
Exit Function
End If
On Error GoTo 0
' sApp = UCase(sApp)
Select Case sApp
Case "WORD"
sID = "{00020905-0000-0000-C000-000000000046}"
Case "OUTLOOK"
sID = "{00062FFF-0000-0000-C000-000000000046}"
Case "ACCESS"
sID = "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}"
Case "EXCEL"
sID = "{00020813-0000-0000-C000-000000000046}"
Case Else
MsgBox sApp & " not known"
End Select
If Len(sID) = 0 Then Exit Function
For Each ref In refs
If ref.GUID = sID Then
Exit Function
End If
Next
On Error GoTo errH
refs.AddFromGuid sID, 0, 0 'Makes error here often?
Exit Function
errH:
MsgBox "Error setting ref to " & sApp
End Function
Function RemoveOfficeRef(wb As Workbook, strReference As String)
Dim ref2 As Object
Dim refs2 As Object
Dim c As String
On Error Resume Next
Set refs2 = wb.VBProject.References
If refs2 Is Nothing Then
MsgBox "need to change security settings"
Exit Function
End If
On Error GoTo 0
For Each ref2 In refs2
Err.Number = 0
On Error Resume Next 'Doesn't work with MISSING:WORD
If ref2.Name = strReference Then
wb.VBProject.References.Remove ref2 'Makes error here often?
Exit Function
End If
On Error GoTo 0
If Err.Number = 1 Then wb.VBProject.References.Remove ref2
Next
End Function