Export from Excel to a word Document and insert a

Question

I have the following code:

    Sub CreateRapport()

    Dim wdApp As Object
    Dim wd As Object

    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set wd = wdApp.Documents.Add

    wdApp.Visible = True



Sheets("Rapport").Activate
Set Rng = ThisWorkbook.ActiveSheet.Range("A1:E76")

Rng.Copy

   With wd.Range
        .Collapse Direction:=0                  'Slutet av dokumentet
        .InsertParagraphAfter                   'Lägg till rad
        .Collapse Direction:=0                  'Slutet av dokumentet
        .PasteSpecial False, False, True        'Pasta som Enhanced Metafile

    End With

End Sub

What it does is that it creates a word document with the data from range A1:E76

I want to insert a header in this word document that contains a picture and a name. The name in this header is in cell A1 in the same sheet.

Would be very thankful if anyone could help me with this. Thank you.

Answer 1

How to add text from cell A1 to the document:

wdApp.Selection.TypeText ThisWorkbook.ActiveSheet.Range("A1").Text

How to turn the current text into a header:

'***** Word VBA constant wdStyleHeading1 = -2
wdApp.Selection.Style = -2

How to add an image:

wdApp.Selection.InlineShapes.AddPicture Filename:="PATH_TO_IMAGE", LinkToFile:=False, SaveWithDocument:=True

If you put the above code together and add it directly after the line wdApp.Visible = True you will get a header with an image at the end, but I can't tell from your question exactly how you want the document to look.

EDIT

Code to show current header:

'***** Word VBA constant wdSeekCurrentPageHeader = 9
wdApp.ActiveWindow.ActivePane.View.SeekView = 9

Show normal view:

'***** Word VBA constant wdSeekMainDocument  = 0
wdApp.ActiveWindow.ActivePane.View.SeekView = 0

Putting it all together, paste this after the line wdApp.Visible = True, this time without setting the style:

wdApp.ActiveWindow.ActivePane.View.SeekView = 9
wdApp.Selection.TypeText ThisWorkbook.ActiveSheet.Range("A1").Text
wdApp.Selection.InlineShapes.AddPicture Filename:="PATH_TO_IMAGE", LinkToFile:=False, SaveWithDocument:=True
wdApp.ActiveWindow.ActivePane.View.SeekView = 0

EDIT 2

My suggestion about transferring the embedded image from Excel to Word is to use copy and paste:

'***** copy image from cell B1 in Excel
ThisWorkbook.ActiveSheet.Range("B1").Copy
'***** past image at the current position in Word
wdApp.Selection.Paste

The code needs to go somewhere near the wdApp.Selection.TypeText command, depending on where you want the image to display.

EDIT 3

Code for adding page number field:

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:="PAGE  ", PreserveFormatting:=True