References: How to removed MISSING: WORD..

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top