Copying tables from excel to word

Question

I'm trying to take a word document and populate placeholders but using the Find and ReplaceAll method (which works just fine) and then copy in a table from excel into the the word document - The intent is to find the placeholder for the table, then replace it but I'm coming undone in getting the table in the correct place (replacing the place holder).

Here's my code:

Sub Test()
Dim i As Long, iManager As Long, iFunds As Long
Dim sM As String
Dim WS As Worksheet, WS1 As Worksheet
Dim sManager() As String
Dim objWord

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

Set WS = ThisWorkbook.Sheets("Investment Manager details")
Set WS1 = ThisWorkbook.Sheets("Fund details")
Set WS2 = ThisWorkbook.Sheets("Worksheet")

ReDim sManager(1 To (WS.Range("A" & WS.Rows.Count).End(xlUp).Row - 1), 1 To 2)

For iManager = 1 To UBound(sManager)
    sM = iManager
    sManager(iManager, 1) = WS.Range("A" & iManager + 1).Value
    sManager(iManager, 2) = WS.Range("B" & iManager + 1).Value
    iFunds = WorksheetFunction.CountIf(WS1.Range("H:H"), sManager(iManager, 1))
    objWord.Documents.Open "C:\Users\Jeremy\Documents\Manager Documents\Template.doc"
    With objWord.ActiveDocument.Content.Find
      .Text = "%Manager.Name%"
      .Replacement.Text = sManager(iManager, 1)
      .Replacement.ClearFormatting
      .Replacement.Font.Italic = False
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
      .Execute Replace:=wdReplaceAll
    End With
    WS1.Range("C50:D52").Copy                                                       'This is just for example - the actual table will vary in size so can be included in the template
    objWord.Selection.GoTo What:=wdGoToBookmark, Name:="Table"
    objWord.Selection.PasteExcelTable False, False, False                           
Next
iManager = 0


objWord.Quit
Set objWord = Nothing

End Sub

How can I get the placeholder and put the pasted table in the right place?

have a look at [SO: Generate Word Documents (in Excel VBA) from a series of Document Templates](https://stackoverflow.com/questions/5106743/generate-word-documents-in-excel-vba-from-a-series-of-document-templates) - someone has already done it, plus I built an application in c# to do that... — Our Man in Bananas, Aug 17 '18 at 09:31
no, it won't - take it one step at a time, start with small pieces, get them to work, and add new pieces... — Our Man in Bananas, Aug 17 '18 at 10:09
also, take a look at [The Spreadsheet Guru: Copy-paste an Excel table into Word](https://www.thespreadsheetguru.com/blog/2014/5/22/copy-paste-an-excel-table-into-microsoft-word-with-vba) and [MS Docs: PasteExcelTable Method](https://learn.microsoft.com/en-us/office/vba/api/Word.Selection.PasteExcelTable) — Our Man in Bananas, Aug 17 '18 at 10:11
@OurManinBananas Thanks for the confidence but I'm not so sure! I think if I could find a way of selecting a specific string (the placeholder) that would be enough - my code works, it just put the table in the wrong place as the selection defaults to the beginning of the document and I haven't figured out how to change that.. probably very simple but I'm not familiar with ms word functions. — Jeremy, Aug 17 '18 at 10:19
I think your problem may be in the way you're using `Find` - what happens when you step through it using F8 (showing the effect of the `Find`)? — Our Man in Bananas, Aug 17 '18 at 10:23
@OurManinBananas Literally nothing - There's not even an error! if I skip that line it also has the exact same outcome — Jeremy, Aug 17 '18 at 10:25
hmmm (LOL) if this `objWord.ActiveDocument.Content.Find.Execute findText:="%Table%", Forward:=True` finds a **selection**, then you need to **copy** the selection before you `objWord.Selection.PasteExcelTable False, False, False` ? — Our Man in Bananas, Aug 17 '18 at 10:28
@OurManinBananas no, I'm copying from the workbook, not the selection - I figured it out - Bookmark the placeholder and the you can select the bookmark :D — Jeremy, Aug 17 '18 at 10:37
Glad that you found the answer - please delete the answer, or edit it with the full solution and what was wrong — Our Man in Bananas, Aug 17 '18 at 10:48

score 0 · Answer 1 · answered Aug 17 '18 at 23:23

0

Replace:

bjWord.Selection.GoTo What:=wdGoToBookmark, Name:="Table"
objWord.Selection.PasteExcelTable False, False, False

with:

objWord.ActiveDocument.Bookmarks("Table").Range.PasteExcelTable False, False, False

There is no need to 'Select' anything.

answered Aug 17 '18 at 23:23

macropod

12,757
2
9
21

Copying tables from excel to word

1 Answers1