39666

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

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.

Answer1:

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.

<strong>EDIT</strong>

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

Recommend

  • 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?