Excel Macro to Format Word Document

[bisel]

Hello All,

I have a macro in Excel which creates a Word document and then copies and pastes several ranges from Excel to Word. When pasting to Word, the result is a table ... which is what I want.

The macro works as I wish.

Sub CompDetails_to_Word()


Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim myTable As Table
Dim detailnum As String
Dim shownum As String


Dim i As Integer


i = 1


please_wait.Show ' Open user form to display the please wait message
please_wait.Repaint ' repaint required


On Error GoTo disperrtext




Set wdApp = New Word.Application ' Forces new Word application every time.  Prevent error 462.
Set wdDoc = wdApp.Documents.Add


Application.CutCopyMode = False 'clear clipboard


wdApp.Visible = True
   
    
' Set up the Word document layout
With wdDoc
    .PageSetup.Orientation = 1 'wdOrientLandscape
    .PageSetup.LeftMargin = wdApp.InchesToPoints(0.5)
    .PageSetup.RightMargin = wdApp.InchesToPoints(0.5)
    .PageSetup.TopMargin = wdApp.InchesToPoints(0.5)
    .PageSetup.BottomMargin = wdApp.InchesToPoints(0.5)
    
For i = 1 To 250 ' For each component item from 1 to 250


detailnum = "detail" & i
shownum = "show" & i


' Check if the item is included in the component list, if not skip
    If Range(shownum).Value = 1 Then
        Range(detailnum).Copy ' copy range from Excel
            .Range.Characters.Last.Paste ' paste to Word as a table
        Application.CutCopyMode = False 'clear clipboard
            .Range.InsertAfter vbCr ' Insert new line after each table
        Else
    End If


Next i


End With 'wdDoc
  
Application.CutCopyMode = False 'clear clipboard


Unload please_wait


wdApp.Activate ' Bring the Word document to front.


    Set wdApp = Nothing
    Set wdDoc = Nothing
    Set myTable = Nothing


Exit Sub


disperrtext:  ' if error creating Word document, display msg


    Unload please_wait
      
    Set wdApp = Nothing
    Set wdDoc = Nothing


    msg = Range("error_msg_create_word_doc").Value
            Style = vbOKOnly + vbInformation
            Title = "Error Creating Word Document"
            response = MsgBox(msg, Style, Title)




End Sub

I get good results but with one issue ...

The resulting Word document with the tables allows rows to break across pages. I wish to keep each table together. I can manually select each table and then edit the table row properties to uncheck the box which allows the table to break across pages, but I want to do this with VBA in the Excel macro.

I have tried several times to select each table, but because the table has some vertical cells merged, I am unable to select the table using VBA.

For an alternative, I have tried to select all text in the resulting Word document and then change the paragraph property to keep with next. But this method, which achieves my goal if I do it manually, fails when included in the VBA code.

Appreciate any help.

Regards,

Steve

 

[Macropod]

After:
.Range.Characters.Last.Paste ' paste to Word as a table
insert:
.Tables(.Tables.Count).Rows.AllowBreakAcrossPages = False

 

[bisel]

Thank you very much, Paul. Works like a charm.

Steve