Nsryjdtyk

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.

1. 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?




2. 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.




3. 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

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.