Inserting excel tables into specific locations in a word document using VBA

[reddevil1493]

Hi,
I have been trying to insert four excel tables into a single page in a word document; to be placed at specific locations. The issue I'm facing is that the code until the selection of summaryVal works fine; but nothing after. In spite of setting the right table from excel when I paste it in word, and try to format it; I do not get an output/ I get the error "Object required". I have defined paragraphs to fit the new table as well. What am I doing wrong?

My code is as follows :

ub ExcelRangeToWord()
 Dim tbl As Excel.Range
 Dim WordApp As Word.Application
 Dim myDoc As Word.Document
 Dim WordTable As Word.Table
 Dim WordTable1 As Word.Table
 
 Dim tbl2 As Excel.Range
 
 Dim nRows
 Dim nCols
 Dim nRows1
 Dim nCols1
 Dim myDocRange


 Dim strval As String
 Dim summaryVal As String
 
'Copy Range from Excel
  Set tbl = ThisWorkbook.Worksheets("Summary").ListObjects("Table1").Range


'Create an Instance of MS Word
  On Error Resume Next
    
    'Is MS Word already opened?
      Set WordApp = GetObject(Class:="Word.Application")
    
    'Clear the error between errors
      Err.Clear


    'If MS Word is not already open then open MS Word
      If WordApp Is Nothing Then Set WordApp = CreateObject(Class:="Word.Application")
    
    'Handle if the Word Application is not found
      If Err.Number = 429 Then
        MsgBox "Microsoft Word could not be found, aborting."
        GoTo EndRoutine
      End If


  On Error GoTo 0
  
'Make MS Word Visible and Active
  WordApp.Visible = True
  WordApp.Activate
    
'Create a New Document
  Set myDoc = WordApp.Documents.Add
  
  
  myDocRange = myDoc.Range
  
'Copy Excel Table Range
  tbl.Copy


'Paste Table into MS Word
  myDoc.Paragraphs(1).Range.PasteExcelTable _
    LinkedToExcel:=True, _
    WordFormatting:=False, _
    RTF:=False


 
  
    
 
'Autofit Table so it fits inside Word Document
  Set WordTable = myDoc.Tables(1)
  WordTable.AutoFitBehavior (wdAutoFitWindow)
   
  WordTable.Rows.HorizontalPosition = 5
  WordTable.Rows.VerticalPosition = 100
  nRows = WordTable.Rows.Count
  nCols = WordTable.Columns.Count
  
  'format the table
  
 ' With WordTable
  '.PreferredWidthType = wdPreferredWidthPercent
  '.PreferredWidth = 100
  '.Columns(1).Width = 20
 ' .Columns(2).Width = 20
  '.Columns(4).Width = 20
  '.Columns(5).Width = 20
 ' End With
  
   For i = 1 To nRows
    For j = 1 To nCols
        WordTable.Cell(i, j).Range.Font.Name = "Calibri"
        WordTable.Cell(i, j).Range.Font.Size = 12
        
            If j = 1 Then
                WordTable.Cell(i, j).Range.Shading.BackgroundPatternColor = RGB(176, 196, 222)
            ElseIf j = 2 Then
                WordTable.Cell(i, j).Range.Shading.BackgroundPatternColor = RGB(216, 191, 216)
            ElseIf j = 3 Then
                WordTable.Cell(i, j).Range.Shading.BackgroundPatternColor = RGB(238, 232, 170)
            ElseIf j = 4 Then
                WordTable.Cell(i, j).Range.Shading.BackgroundPatternColor = RGB(222, 184, 135)
            ElseIf j = 5 Then
                WordTable.Cell(i, j).Range.Shading.BackgroundPatternColor = RGB(143, 188, 143)
            End If
    Next
  Next




'Add the commentary section '


 strval = ThisWorkbook.Worksheets("Summary").Range("D2").Value
 
 summaryVal = ThisWorkbook.Worksheets("Description").Range("B2").Value
 'WordApp.Selection.TypeText Text:=strval
With WordApp.Selection
   .ParagraphFormat.Alignment = wdAlignParagraphCenter
    .PageSetup.TopMargin = WordApp.InchesToPoints(0.25)
   .Font.Bold = True
    .Font.Name = "Arial"
    .Font.Size = 32
    .Font.Color = RGB(100, 149, 237)
    .TypeText (strval)
    .TypeParagraph
End With




'Dim ioVal
'ioVal = ThisWorkbook.Worksheets("IO").Range("B3").Value
'WordApp.Visible = True


With WordApp.Selection
    .ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Font.Bold = True
    .Font.Name = "Arial"
    .Font.Size = 14
    .Font.Color = RGB(100, 149, 237)
    .TypeText (summaryVal)
    .TypeParagraph
End With


WordApp.Visible = True
'With WordApp.Selection
 '   .ParagraphFormat.Alignment = wdAlignParagraphRight
 '   .Font.Bold = True
  '  .Font.Name = "Arial"
   ' .Font.Size = 14
   ' .Font.Color = RGB(100, 149, 237)
   ' .TypeText (ioVal)
   ' .TypeParagraph
'End With


' Insert second Paragraph'


myDocRange.Collapse Direction:=wdCollapseEnd
With myDocRange
    .Collapse Direction:=wdCollapseEnd
    .InsertParagraphAfter
    .Collapse Direction:=wdCollapseEnd
End With




Set tbl2 = ThisWorkbook.Worksheets("IO").ListObjects("Table6").Range


tbl2.Copy


 myDoc.Paragraphs(2).Range.PasteExcelTable _
    LinkedToExcel:=True, _
    WordFormatting:=False, _
    RTF:=False


'Autofit Table so it fits inside Word Document
  Set WordTable1 = myDoc.Tables(2)
  WordTable1.AutoFitBehavior (wdAutoFitWindow)
   
  'WordTable1.Rows.HorizontalPosition = 5
  'WordTable1.Rows.VerticalPosition = 500
  nRows1 = WordTable1.Rows.Count
  nCols1 = WordTable1.Columns.Count
  WordTable1.Cell(1, 1).Range.Font.Name = "Arial"
 WordTable1.Cell(1, 1).Range.Font.Size = 18
  WordTable1.Cell(1, 1).Range.Font.Color = RGB(100, 149, 237)
  






'Dim iProfile


'iProfile = ThisWorkbook.Worksheets(4).Range("B2").Value


'With WordApp.Selection


'WordApp.Selection.Range(strval).Bold = True
'WordApp.Selection.Range(strval).Font.Name = "Arial"
' WordApp.Selection.Range(strval).Font.Size = 32


            


EndRoutine:
'Optimize Code
  Application.ScreenUpdating = True
  Application.EnableEvents = True


'Clear The Clipboard
  Application.CutCopyMode = False


 End Sub

 

[LateralThinker]

You missed 'set'

SET myDocRange = myDoc.Range

 

[reddevil1493]

thanks for pointing that out! In general though, is the block of code I used to paste tables in excel correct/ is this the right approach to do this?

You missed 'set'

SET myDocRange = myDoc.Range

 

[Macropod]

Not entirely clear what you're trying to do, but you might start with:

Sub ExcelRangeToWord()
Dim wdApp As New Word.Application, wdDoc As Word.Document, wdTbl As Word.Table
Dim r As Long, c As Long, lClr As Long
 
'Create a New Document
Set wdDoc = wdApp.Documents.Add

'Paste Table into MS Word
With wdDoc
  .PageSetup.TopMargin = wdApp.InchesToPoints(0.25)
  'Copy Range from Excel
  Worksheets("Summary").ListObjects("Table1").Range.Copy
  .Paragraphs.Last.Range.PasteExcelTable LinkedToExcel:=True, WordFormatting:=False, RTF:=False
  'Clear The Clipboard
  Application.CutCopyMode = False
 
  Set wdTbl = .Tables(.Tables.Count)
  With wdTbl
    'Autofit Table so it fits inside Word Document
    .AutoFitBehavior (wdAutoFitWindow)
    .Rows.HorizontalPosition = 5
    .Rows.VerticalPosition = 100
    With .Range.Font
      .Name = "Calibri"
      .Size = 12
    End With
    For r = 1 To .Rows.Count
      For c = 1 To .Columns.Count
        Select Case c
          Case 1: lClr = RGB(176, 196, 222)
          Case 2: lClr = RGB(216, 191, 216)
          Case 3: lClr = RGB(238, 232, 170)
          Case 4: lClr = RGB(222, 184, 135)
          Case 5: lClr = RGB(143, 188, 143)
          Case Else: lClr = RGB(255, 255, 255)
        End Select
        .Cell(r, c).Range.Shading.BackgroundPatternColor = lClr
      Next
    Next
  End With
  With .Paragraphs.Last.Range
    .ParagraphFormat.Alignment = wdAlignParagraphCenter
    With .Font
      .Bold = True
      .Name = "Arial"
      .Size = 32
      .Color = RGB(100, 149, 237)
    End With
    .InsertAfter Worksheets("Summary").Range("D2").Value & vbCr
  End With
  With .Paragraphs.Last.Range
    .ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Font.Size = 14
    .InsertAfter Worksheets("Description").Range("B2").Value & vbCr & vbCr
  End With
  Worksheets("IO").ListObjects("Table6").Range.Copy
  .Paragraphs.Last.Range.PasteExcelTable LinkedToExcel:=True, WordFormatting:=False, RTF:=False
  'Clear The Clipboard
  Application.CutCopyMode = False
  Set wdTbl = .Tables(.Tables.Count)
  With wdTbl
    'Autofit Table so it fits inside Word Document
    .AutoFitBehavior (wdAutoFitWindow)
    With .Range.Font
      .Name = "Arial"
      .Size = 18
      .Color = RGB(100, 149, 237)
    End With
  End With
End With
With wdApp
  .Visible = True
  .Activate
End With
Set wdTbl = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing
End Sub

 

[reddevil1493]

Thank you! What does the following line do?
Set wdTbl=.Tables.(.Tables.Count)
Also, if I wanted to have this table and add two other tables which need to be explicitly placed with (x,y) coordinates instead of using wdAlignParagraph, how would I be able to do so?

 

[reddevil1493]

Running a sequence of steps to extract excel tables to a word document based on a condition

Hi,
Say I have Sub ExcelRangeToWord which contains a series of steps that extract Excel tables and place them at specific locations on a word document;
1. how would I be able to wrap this method under a condition that checks if a 6 digit code is the same as the value in an excel cell?
2. if I want to replicate this conditional execution for an entire array of 6-digit codes(array length : 150), how would I build a looping structure? essentially every time a condition is met, a new word document with the corresponding data from excel is generated
3. Would it also be possible to build it so that each time the excel workbook containing the data is opened, all 150 word documents are produced and saved in the same folder?How?

 

[Macropod]

Thank you! What does the following line do?
Set wdTbl=.Tables.(.Tables.Count)

It points the wdTbl variable to the last table in the document (i.e. the one the code just created). One could replace .Tables.Count with the table's index (e.g. 1, 2, etc.), but this approach avoids having to think about the indexing.

Also, if I wanted to have this table and add two other tables which need to be explicitly placed with (x,y) coordinates instead of using wdAlignParagraph, how would I be able to do so?

The code I posted already shows how to do that - using your own code as the basis - for the first table. It's not apparent why you'd want to do that, though, when you're creating a document from scratch and, presumably, you'd want the text you're adding to be placed above or below the table. With the code for the first table's explicit positioning, the text ends up above it, even though the text was added below the table.

PS: Please don't quote entire posts in your replies - doing so just adds clutter. If you need to reference something particular, quote just that portion in your reply.

 

[reddevil1493]

Got it, thanks! As a follow up, how do I ensure that a line is left after each insertion? At present, the table for Description value is immediately after the one for the summary value

Also, how would I adjust the column width of the table? I am ideally trying to get 2 tables next to each other, i.e., start on the same line; following the table with "Description" value

My ultimate goal is to have these word documents/reports that specify information about financial portfolios built every time the corresponding data in excel is refreshed(once a month). While I don't need to automate the data upload process, what I am looking to do is either get
a) all of the word reports( around 150 in number) published(and ideally saved) everytime the data is refreshed or an ActiveX button is clicked. Each portfolio has a 6 digit code which can be used as a reference key. As such, I would like to have the code block for transferring the tables/formatting the document wrapped under a condition to check if the code matches/exists. The way the excel table is set up is; the code followed by the corresponding info in the very next column.
OR
b) have a button for each portfolio/code, which upon being clicked produces the corresponding word report

 

[Macropod]

As a follow up, how do I ensure that a line is left after each insertion? At present, the table for Description value is immediately after the one for the summary value

ideally, you'd control all the paragraph formatting, including fonts, alignments & space before/after via Styles, for which you'd define a Word template containing them that your code could call when using wdApp.Documents.Add.
A fall-back position would be to apply a space before/after setting to the Summary paragraph or Description paragraph directly. Cruder still would be to insert an empty paragraph between them.

Also, how would I adjust the column width of the table?

You could adjust the 'preferred width' for each column individually, or for the table as a whole. For the latter you might use something like:

  With wdTbl
    .AllowAutoFit = False
    .PreferredWidthType = wdPreferredWidthPercent
    .PreferredWidth = 45

I am ideally trying to get 2 tables next to each other, i.e., start on the same line; following the table with "Description" value

IMHO you'd do far better to create a suitable table in Excel (at least temporarily) containing both sets of data and paste them into Word as a unit. Otherwise, you'll have to manipulate the .Rows.HorizontalPosition and .Rows.VerticalPosition settings for both tables, keeping in mind the consequences for how the rest of the text is going to be positioned around them (see my comments in my previous reply). Alternatively, you'd have to insert Section breaks and apply a two-column page layout to the portion of the document that's supposed to contain the paired tables.

My ultimate goal is to have these word documents/reports that specify information about financial portfolios built every time the corresponding data in excel is refreshed(once a month).

This is starting to sound like something that OLE, perhaps combined with the use of DATABASE fields, would be better suited to. With OLE, you'd copy & paste the Excel ranges into a prepared Word document, with the 'paste link' option and your preferred paste format. From then on, the data in the Word document will auto-update whenever the Excel data changes. No code required. The links can work with cell addresses or named ranges. DATABASE fields are useful for creating tables linked to an Excel file where the Excel data, though laid out in a tabular format, are not necessarily sorted. Again, no code required. For an example of DATABASE field implementation, see: http://www.msofficeforums.com/mail-merge/21847-mail-merge-into-different-coloumns.html#post67097