Paste From Excel to Word INCL. Header/Footer

[Helene]

Hello,

I am using a code from Jakob Hildbrand to paste my Excel sheet into Word (Thank you very much, simple but very useful):
Option Explicit

Sub PasteToWord()

Dim AppWord As Word.Application

Set AppWord = CreateObject("Word.Application")
AppWord.Visible = True

Sheets("Convert").UsedRange.Copy
AppWord.Documents.Add
AppWord.Selection.Paste

Application.CutCopyMode = False

Set AppWord = Nothing

End Sub

As my Excel sheet is formatted in a DINA4 page and has margins of 1.5 cm
at the top/bottom/left and right, the pasted word sheet excludes the
Headers and Footers of the Excel.
Do you have any idea how I can
extend the code/range to include also the Header/Footer in the paste
process? Do I have to use somehting like "With...Sheet("Convert to
Word").LeftHeader = docWord.LeftHeader"? I've been trying it out all
day, but I can't find the right solution. Maybe you can give me a hint
on howI can solve this problem?

Best regards, Helene

[Dave]

Hi Helene and Welcome to this forum. If U want to place info from XL in Word's actual header and footer spaces then U need to code for it.
Here's some code that has footer text in sheet Convert!A1 and header text in Convert!A2. HTH. Dave

Sub PasteToWord()
Dim AppWord As Object, oHF As Object
Dim rHeader As Object, FtStr As String
On Error GoTo erfix
Set AppWord = CreateObject("Word.Application")
AppWord.Visible = True
Sheets("Convert").UsedRange.Copy
AppWord.Documents.Add
AppWord.Selection.Paste
Application.CutCopyMode = False
'footer
FtStr = CStr([Convert!A1]) & vbTab & vbTab & Format(Now(), "mmm d yyyy") _
& " " & Format(Now(), "hh:mm:ss AMPM") 'footer stuff here
AppWord.ActiveDocument.Sections(1).Footers(1).Range.Text = FtStr
AppWord.ActiveDocument.Sections(1).PageSetup. _
DifferentFirstPageHeaderFooter = False
'header
Set oHF = AppWord.ActiveDocument.Sections(1).Headers(1)
With oHF
.LinkToPrevious = False
Set rHeader = oHF.Range
With rHeader
.Text = CStr([Convert!A2]) 'header stuff here
.Collapse Direction:=0
.Fields.Add Range:=rHeader
End With
End With
Set rHeader = Nothing
Set oHF = Nothing

'set print preview for headers
AppWord.ActiveDocument.ActiveWindow.View.Type = 3
AppWord.ActiveDocument.ActiveWindow.View.Zoom.Percentage = 100
Exit Sub
erfix:
On Error GoTo 0
MsgBox "File error"
AppWord.Quit
Set AppWord = Nothing
End Sub
ps. please use code tags

[Helene]

HI,

Thank you a lot, that works fine! Is there any chance to add the page number as a vbTab? I tried to simple ad "PageNumber" but it only gives me the text back?
Thanks for the support,
Hel

[Dave]

You are welcome Hel. Change this part for page numbering...


With rHeader
.Text = CStr([Convert!A2]) & "Page "
.Collapse Direction:=0
.Fields.Add Range:=rHeader, Type:=33
End With
Dave

[Helene]

Hi,
thanks that works fine, but is there any possibility to put the page number in the footer or in the middle/left side of the page?
And one last question: How can I formate the footers e.g. a specific font name?

Again thanks for the help!!
Helene

[Dave]

Again you are welcome Helene. As far as your remaining questions, they are beyond the scope of my knowledge. Hopefully, others will be able to help. Good luck. Dave

[Helene]

Hi,
have one addtional question.
Is there a possiblity to copy the range in a predefined Word template, that is safed on the desktop of different users, E.g. by searching for the name. I tried it with the path AppWord.Documents. Add "C:Users,.../Template.docx" but It can not find the document.
Thanks in advance,
Hel