Excel VBA copy range from Excel and paste it to Word header Text Box

Question

I have Excel Workbook from where I am running following code below. I have logo and page numbering already in Word document so I do not need to paste the whole range from Excel. I have two Text Boxes where data from spreadsheet should be inserted.

I need to copy Worksheets("Other Data").Range("A58:A60") and paste it to "Text Box 1" that I have in Word documents header. Three sentances on different rows. Text Box should be wrapped?
I need to copy Worksheets("Other Data").Range("A68") and paste it to "Text Box 2" that I have in Word documents header. One sentance.
AutoFitWindows doesn't work. There have to be something with variables but I can't figure what exactly is wrong. Tried different ways with no success.

Here is my code:

Sub excelToWord_click()

    Dim head As Excel.Range
    Dim foot As Excel.Range
    Dim WordTable As Word.Table
    Set wdApp = CreateObject("Word.Application")
    wdApp.Documents.Open FileName:=ThisWorkbook.Path & "\" & "MyDOC" & ".docx"
    wdApp.Visible = True

    Set head = ThisWorkbook.Worksheets("Other Data").Range("A58:A60")

    head.Copy

    '|| I need to paste copied cells to "Text Box 1" in my Word document ||'

    With wdApp.ActiveDocument.Sections(1)
        .Headers(wdHeaderFooterIndex.wdHeaderFooterPrimary).Range.Shapes("Text Box 1").Activate
        head.Paste
    End With

    '|| ---------------------------------------------------------------- ||'

        Set head2 = ThisWorkbook.Worksheets("Other Data").Range("A68")

    head2.Copy

    '|| I need to paste copied cells to "Text Box 2" in my Word document ||'

    With wdApp.ActiveDocument.Sections(1)
        .Headers(wdHeaderFooterIndex.wdHeaderFooterPrimary).Range.Shapes("Text Box 2").Activate
        head2.Paste
    End With

    '|| ---------------------------------------------------------------- ||'

        Set foot = ThisWorkbook.Worksheets("Other Data").Range("A62:H65")
    foot.Copy

    With wdApp.ActiveDocument.Sections(1)
    .Footers(wdHeaderFooterIndex.wdHeaderFooterPrimary).Range.Paste
    End With

    '|| Autofit table to page in Footer ||'

    WordTable.AutoFitBehavior (wdAutoFitWindow)

    '|| ---------------------------------------------------------------- ||'

    'restore Word
    If wdApp.ActiveWindow.View.SplitSpecial <> 0 Then
        wdApp.ActiveWindow.Panes(2).Close
    End If
    If wdApp.ActiveWindow.ActivePane.View.Type = 1 _
    Or wdApp.ActiveWindow.ActivePane.View.Type = 2 Then
        wdApp.ActiveWindow.ActivePane.View.Type = 3
    End If
    wdApp.WordBasic.AcceptAllChangesInDoc
    'wdApp.ActiveDocument.PrintOut, Copies:=1

    wdApp.ActiveDocument.ExportAsFixedFormat outputfilename:=ThisWorkbook.Path & "\" & Sheets("MAIN").Range("D14").Value & ", " & Sheets("MAIN").Range("D11").Value & "_" & "Document" & "_" & ".pdf", exportformat:=wdExportFormatPDF

    wdApp.ActiveDocument.SaveAs ThisWorkbook.Path & "\" & Worksheets("MAIN").Range("D14").Value & ", " & Worksheets("MAIN").Range("D11").Value & "_" & "Document" & "_" & ".docx"

        wdApp.Quit '<--| quit Word
    Set wdApp = Nothing '<--| release object variable
    'wdApp.ActiveWindow.Close savechanges:=False
End Sub

I recommend to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration and you will see your issue immediately. — Pᴇʜ
@user7202022 Its also helpful to check that syntax checking is enabled. In the VB IDE goto Tools.Options.Editor and make sure that all the check boxes in the Code Settings pane are ticked. Some people quibble about the need for the Auto syntax check box as it can be annoying but its a starting point. Turn it off later if it annoys you. — freeflow
Cross-posted at: mrexcel.com/forum/general-excel-discussion-other-questions/…. For cross-posting etiquette, please read: excelguru.ca/content.php?184 — macropod

freeflow freeflow · Accepted Answer · 2018-11-20T14:35:59

Your problem is because you are late binding your word application object rather than installing the Word reference to the VBA IDE. This means that any references to word constants without qualification to the variable you are using for your word app will be interpreted as the default (0 or Null) value.

The simplest way to resolve this issue is in the VBA IDE; goto Tools.References and make sure that the check box next to Microsoft Word ...... is ticked.

If you would prefer to qualify your variables then you need to change word constants so that they are prefixed with WdApp, your variable for the Word Application.

e.g. wdApp.wdHeaderFooterIndex.wdHeaderFooterPrimary

With the Word reference installed you can just say

wdHeaderFooterPrimary.

Excel VBA copy range from Excel and paste it to Word header Text Box

1 Answers