Consulting

Results 1 to 7 of 7

Thread: Paste From Excel to Word INCL. Header/Footer

  1. #1
    VBAX Newbie
    Joined
    May 2019
    Posts
    4
    Location

    Paste From Excel to Word INCL. Header/Footer

    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

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    829
    Location
    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

  3. #3
    VBAX Newbie
    Joined
    May 2019
    Posts
    4
    Location
    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

  4. #4
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    829
    Location
    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

  5. #5
    VBAX Newbie
    Joined
    May 2019
    Posts
    4
    Location
    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

  6. #6
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    829
    Location
    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

  7. #7
    VBAX Newbie
    Joined
    May 2019
    Posts
    4
    Location
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •