Excel VBA to copy tables from 4 tabs in excel and paste in new word document - Formatting issues

[LauraBlair]

Hi All,

I'm really hoping you may be able to help me. I have a VBA code that copies the tables from 4 tabs in the excel document, and then opens up a new word document and pastes into Word to form an 'Executive Summary'. This all works well, but the formatting of headings and boxes in the word doc that would require data input, is small and would like these cells in the table to merge so the text doesn't look so cluttered. I am struggling to get anything in the code to work in terms of this, I am starting to wonder if copy/pasting in tables is not the best way to do this. Could anyone please help? Thank you

Sub Executive_Summary()
'
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim myTable As Table


With wdApp
  .Visible = True
  
  Set wdDoc = .Documents.Add
  
' Set up layout
  With wdDoc
    
' Copy the first range
    Range("FrontPage").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter Chr(12) ' Insert new line before next table
    wdApp.Selection.SplitTable
    wdApp.Selection.TypeParagraph
    wdApp.Selection.TypeParagraph
    
' Before second table, insert column and format table
    Set myTable = wdDoc.Tables(1)
        With myTable
            .Columns.Add ' Adds a column to the right
            .AutoFitBehavior wdAutoFitWindow 'Autofit table to window
            .Range.Font.Size = 25
            .Range.Font.Bold = False
        End With
            
' Copy second range
    Range("Page2").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter Chr(12) ' Insert new line
    
' Before third table, insert column and format table
    Set myTable = wdDoc.Tables(2)
        With myTable
            .Columns.Add ' Adds a column to the right
            .AutoFitBehavior wdAutoFitWindow 'Autofit table to window
            .Range.Font.Size = 9
            .Range.Font.Bold = False
        End With
    
' Copy third range
    Range("Page3").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter Chr(12) ' Insert new line
    
' Before forth table, insert column and format table
    Set myTable = wdDoc.Tables(3)
        With myTable
            .Columns.Add ' Adds a column to the right
            .AutoFitBehavior wdAutoFitWindow 'Autofit table to window
            .Range.Font.Size = 9
            .Range.Font.Bold = False
        End With
    
' Copy forth  range
    Range("Page4").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter Chr(12) ' Insert new line
    
' Insert column and format table
    Set myTable = wdDoc.Tables(4)
        With myTable
            .Columns.Add ' Add column to the right
            .AutoFitBehavior wdAutoFitWindow 'Autofit table to window
            .Range.Font.Size = 9
            .Range.Font.Bold = False
        End With
        
      End With
End With

End Sub

 

[LauraBlair]

Hi all, would anyone be able to assist with the above? I'm getting pretty desperate as my deadline is getting closer lol. I would really appreciate it. Thank you

 

[NdNoviceHlp]

Hi Laura. It doesn't seem like you're having any luck with this one. Not sure if this stuff helps, but U can copy and paste the XL table as a table....

Dim tbl As Range
Set tbl = Sheets("sheet1").ListObjects("Table1").Range
tbl.Copy
wdApp.ActiveDocument.Select
wdApp.Selection.PasteExcelTable _
        LinkedToExcel:=False, _
        WordFormatting:=False, _
        RTF:=False

To format...

Dim TblWdth As Double, ColWdth As Double, WordTbl As Object
'format table
With wdDOC.PageSetup
TblWdth = .PageWidth - .LeftMargin - .RightMargin - .Gutter
ColWdth = TblWdth / 4 'x4 columns
End With
Set WordTbl = .Tables(1)
With WordTbl
.AutoFormat Format:=16, applyborders:=True
.AutoFitBehavior (0)
.Columns.Width = ColWdth
End With
End With

HTH. Dave

 

[LauraBlair]

Hi Laura. It doesn't seem like you're having any luck with this one. Not sure if this stuff helps, but U can copy and paste the XL table as a table....

Dim tbl As Range
Set tbl = Sheets("sheet1").ListObjects("Table1").Range
tbl.Copy
wdApp.ActiveDocument.Select
wdApp.Selection.PasteExcelTable _
        LinkedToExcel:=False, _
        WordFormatting:=False, _
        RTF:=False

To format...

Dim TblWdth As Double, ColWdth As Double, WordTbl As Object
'format table
With wdDOC.PageSetup
TblWdth = .PageWidth - .LeftMargin - .RightMargin - .Gutter
ColWdth = TblWdth / 4 'x4 columns
End With
Set WordTbl = .Tables(1)
With WordTbl
.AutoFormat Format:=16, applyborders:=True
.AutoFitBehavior (0)
.Columns.Width = ColWdth
End With
End With

HTH. Dave

Thanks so much for you reply, formatting still not quite right, but definitely getting close with your help. Thanks

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. It seems that if U are putting 4 tables into the document, that the following code might come in handy. Place this in the code after all tables have been pasted. HTH. Dave

'prevent table from splitting page
Dim Otbl As Object, Ocel As Object
For Each Otbl In wdApp.ActiveDocument.Tables
Otbl.Range.Paragraphs.keepwithnext = True
For Each Ocel In Otbl.Rows.last.Range.Cells
Ocel.Range.Paragraphs.last.keepwithnext = False
Next Ocel
Next Otbl