Export from Excel to a word Document and insert a header in word


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.


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.


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

<strong>EDIT 2</strong>

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.

<strong>EDIT 3</strong>

Code for adding page number field:

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


  • How to draw a rotated text in a Meta file
  • How to copy a picture from canvas to clipboard?
  • How to pass data between ArrayLists?
  • Error getting Images into Listview from server using Json
  • Post JSon data to .NET Object.
  • VBA Excel 2010 - Paste directly from clipboard
  • Excel VBA select method of range class failed
  • VBA Loop combining Lastrow and finding blank values
  • import XML to HTML
  • Using hidden input to preserve form contents
  • Paste special without using clipboard
  • Creating a Countdown Watch with a slider in Xcode
  • getting cpu usage of one process using c++ (win32)
  • Disable email notificaton when sharing a Google Calendar
  • WatiN through TFS on Windows 2008 Server
  • How to animate a circle using bokeh
  • Consecutive calls/evaluations in a form?
  • R - Phylogram labels to vector
  • Learn Ruby Hard Way ex. 48
  • JQueryMobile: SelectWidget not showing after second page visit
  • Convert ctime to unicode and unicode to ctime python
  • Using SQL stored function gives invalid SQL statement
  • AWS S3 implementation for serving private files
  • Lync Database - Mobile vs PC users
  • Touch events (touchesMoved) not working on UIViews inside UIScrollView
  • how to calculate distance between two points in google maps?
  • Put percent labels next to legend instead of in the slice
  • Animating an SVG Group
  • HABTM associations in Rails : collecting and counting the categories of a model's children
  • Guava how to copy all files from one directory to another
  • Does it make sense to call System.gc() and Thread.sleep() when working on Bitmaps?
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • How to stop GridView from loading again when I press back button?
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • Busy indicator not showing up in wpf window [duplicate]
  • costura.fody for a dll that references another dll
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Binding checkboxes to object values in AngularJs
  • How can I use `wmic` in a Windows PE script?