Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Hereford, Herefordshire, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert page break in Word Document created from Excel macro

    I am creating code to run in Excel which has the following purposes
    • To make some format changes to the spreadsheet
    • To create a Word document, copy the entire spreadsheet and paste it into the document
    • To look for certain specific words and insert a page break immediately above those specific words

    The code looks like this

    Code:
    Sub Send2Word()
    ' Send2Word Macro
        Dim wrdApp As Object
        Dim wrdDoc As Object
        Const wdPageBreak = 7
        Application.ScreenUpdating = False
        Application.StatusBar = "Reformatting spreadsheet"
        Cells.Select
        Application.ReplaceFormat.HorizontalAlignment = xlRight
        Selection.Replace What:="", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=True
        Selection.Copy
        Application.StatusBar = "Creating Word document"
        Set wrdApp = CreateObject("Word.Application")
        wrdApp.Visible = True
        Set wrdDoc = wrdApp.Documents.Add
        Application.StatusBar = "Copying spreadsheet"
        wrdApp.Selection.PasteExcelTable False, False, False
        Application.StatusBar = "Paginating document"
        wrdApp.Selection.Find.ClearFormatting
        With wrdApp.Selection.Find
            .Text = "UNAUDITED PROFIT AND LOSS ACCOUNT"
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        wrdApp.Selection.Find.Execute
        wrdApp.Selection.InsertBreak Type:=wdPageBreak
     End Sub
    It works fine up to to the page break - the text exists, but the page break is being put at the end of the document instead of before the words "unaudited profit and loss account". The spreadsheet is posted into Word as a table and I am not sure if this might be the cause of the problem

    There are five specific phrases that I need to search for - which may or may not exist in the document - the code above is to test on the first one

    Any assistance on what I have done wrong will be greatly appreciated.
    Last edited by macropod; 2011-07-22 at 03:00. Reason: Added code tags

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    hi Chris,

    Try something along the lines of:
    Code:
    Sub Send2Word()
     ' Send2Word Macro
     Dim wrdApp As Object
     Dim wrdDoc As Object
     Const wdPageBreak = 7
     Application.ScreenUpdating = False
     Application.StatusBar = "Reformatting spreadsheet"
     Cells.Select
     Application.ReplaceFormat.HorizontalAlignment = xlRight
     Selection.Replace What:="", Replacement:="", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
     ReplaceFormat:=True
     Selection.Copy
     Application.StatusBar = "Creating Word document"
     Set wrdApp = CreateObject("Word.Application")
     wrdApp.Visible = True
     Set wrdDoc = wrdApp.Documents.Add
     Application.StatusBar = "Copying spreadsheet"
     wrdDoc.Selection.PasteExcelTable False, False, False
     Application.StatusBar = "Paginating document"
     With wrdDoc.Content.Find
      .Find.ClearFormatting
      .Replacement.ClearFormatting
      .Text = "UNAUDITED PROFIT AND LOSS ACCOUNT"
      .Replacement.Text = "^m^&"
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
      .Execute Replace:=wdReplaceAll
     End With
     End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Hereford, Herefordshire, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Paul

    Thanks for that - unfortunately it does not work. The issue is not in your code, but in the created document. If I do a normal find and replace with the ^m^& parameters, the page break is not inserted.

    I am guessing it is because the document comes across as a single table which can spread over six to ten pages - once in Word a simple macro to find the text and insert a page break works fine, but achieveing this from within Excel is maybe a step to far.

    Chris

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Chris,

    Try replacing the Find/Replace With ... End With block with:
    Code:
    With wrdDoc.Tables(1)
      Dim Rng As wrdDoc.Range, i As Long
      For i = .Rows.Count To 2 Step -1
        Set Rng = .Rows(i).Cells(1).Range
        Rng.End = Rng.End - 1
        If Rng.Text = "UNAUDITED PROFIT AND LOSS ACCOUNT" Then
          .Split (i)
          Rng.Paragraphs.First.Range.InsertBefore Chr(12)
        End If
      Next
    End With
    Note: the above code assumes the "UNAUDITED PROFIT AND LOSS ACCOUNT" text will be in column 1. If not, change 'Cells(1)' to suit.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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