none
How to add a reference to MS Word in Excel using VBA RRS feed

  • Question

  • I'm experiencing problems when trying to add a reference to the MS Word library using the code string below. I found the code in one of your forums (https://social.msdn.microsoft.com/Forums/office/en-US/46655823-3bb9-4a93-864a-6531a573551b/set-reference-to-library-in-vba?forum=exceldev), but it doesn't seem to work. Also, I have double checked the file path to ensure that it is correct.

    I would appreciate if you could provide me with guidance on how to resolve this issue.  

    Regards

    Daniel

    Sub LabelMerge()

    On Error Resume Next
     Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office15\MSWORD.OLB"

     

        Dim oWord  As Word.Application
        Dim oDoc  As Word.Document
        Dim sPath As String
        Dim i As Integer
        Dim oHeader As Range

    --- Rest of code ---

    Tuesday, July 25, 2017 10:01 AM

All replies


  •   Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office15\MSWORD.OLB"

     

        Dim oWord  As Word.Application

    a) A better way is to use AddFromGuid instead, but...

    b) ...you can not declare variables with a type from that library directly afterwards at runtime.

    I recommend to declare
      Dim oWord  As Object 'Word.Application
    and use GetObject, resp. CreateObject to access the instance of the other application.

    Means use Late binding instead of Early binding.

    Andreas.

    Sub Test()
      Dim oWord As Object 'Word.Application
      Dim oDoc As Object 'Word.Document
      
      On Error Resume Next
      'Get the opened instance
      Set oWord = GetObject(, "Word.Application")
      'Found?
      If oWord Is Nothing Then
        'Create one
        Set oWord = CreateObject("Word.Application")
        'Success?
        If oWord Is Nothing Then
          MsgBox "Word is not accessible"
          Exit Sub
        End If
        Set oDoc = oWord.Documents.Add
        oWord.Visible = True
      End If
    End Sub
    

    Tuesday, July 25, 2017 10:22 AM
  • Thanks for helping out!

    Unfortunately, the method you provided does not seem solve the issue. More specifically, errors seem to occur in the code strings marked in bold (see original version of the full code below). The program works perfect when the Word library is active. Thus, I suspect that several of the errors are due to the failing import of the full library. 

    Sub LabelMerge()
    
        Dim oWord  As Word.Application
        Dim oDoc  As Word.Document
        Dim sPath As String
        Dim i As Integer
        Dim oHeader As Range
      
      
        sPath = ThisWorkbook.FullName
        
        Range(Cells(18, 2), Cells((Range("H3") + 18), 10)).Copy
        Sheets.Add.Name = "Adresser"
        Range("A1").Select
        ActiveSheet.Paste
        Set oWord = CreateObject("Word.Application")
        Set oDoc = oWord.Documents.Add
        oWord.Visible = True
        oDoc.MailMerge.MainDocumentType = wdMailingLabels
        oWord.Dialogs(wdDialogLabelOptions).Show
        oDoc.Activate
        
        
        With oDoc.MailMerge.Fields
        .Add oWord.Selection.Range, "Leverantör"
        oWord.Selection.TypeParagraph
        .Add oWord.Selection.Range, "Adress1"
        oWord.Selection.TypeParagraph
        .Add oWord.Selection.Range, "Adress2"
        oWord.Selection.TypeParagraph
        .Add oWord.Selection.Range, "Adress3"
        oWord.Selection.TypeParagraph
        .Add oWord.Selection.Range, "Postnummer"
        oWord.Selection.TypeParagraph
        .Add oWord.Selection.Range, "Postort"
        oWord.Selection.TypeText "  "
        End With
    
     
        oDoc.MailMerge.OpenDataSource sPath
        oWord.WordBasic.mailmergepropagatelabel
        oDoc.ActiveWindow.View.ShowFieldCodes = False
        oDoc.MailMerge.ViewMailMergeFieldCodes = False
        oDoc.MailMerge.Destination = wdSendToNewDocument
        oDoc.MailMerge.Execute
        Application.DisplayAlerts = False
        Sheets("Adresser").Delete
        Application.DisplayAlerts = True
    
    
    End Sub
    
    

    Tuesday, July 25, 2017 12:32 PM
  • Sub LabelMerge()
      Const wdMailingLabels = 1
      Const wdDialogLabelOptions = 1367
      Const wdSendToNewDocument = 0
      
      Dim oWord  As Object 'Word.Application
      Dim oDoc  As Object 'Word.Document
      Dim sPath As String
      
      sPath = ThisWorkbook.FullName
      Range(Cells(18, 2), Cells((Range("H3") + 18), 10)).Copy
      Sheets.Add.Name = "Adresser"
      Range("A1").Select
      ActiveSheet.Paste
      
      Set oWord = CreateObject("Word.Application")
      Set oDoc = oWord.Documents.Add
      oWord.Visible = True
      oDoc.MailMerge.MainDocumentType = wdMailingLabels
      oWord.Dialogs(wdDialogLabelOptions).Show
      oDoc.Activate
    
      With oDoc.MailMerge.Fields
        .Add oWord.Selection.Range, "Leverantör"
        oWord.Selection.TypeParagraph
        .Add oWord.Selection.Range, "Adress1"
        oWord.Selection.TypeParagraph
        .Add oWord.Selection.Range, "Adress2"
        oWord.Selection.TypeParagraph
        .Add oWord.Selection.Range, "Adress3"
        oWord.Selection.TypeParagraph
        .Add oWord.Selection.Range, "Postnummer"
        oWord.Selection.TypeParagraph
        .Add oWord.Selection.Range, "Postort"
        oWord.Selection.TypeText "  "
      End With
    
      oDoc.MailMerge.OpenDataSource sPath
      oWord.WordBasic.mailmergepropagatelabel
      oDoc.ActiveWindow.View.ShowFieldCodes = False
      oDoc.MailMerge.ViewMailMergeFieldCodes = False
      oDoc.MailMerge.Destination = wdSendToNewDocument
      oDoc.MailMerge.Execute
      
      Application.DisplayAlerts = False
      Sheets("Adresser").Delete
      Application.DisplayAlerts = True
    End Sub
    


    Tuesday, July 25, 2017 1:19 PM
  • Magic! Many thanks for your help!

    Tuesday, July 25, 2017 1:31 PM