Microsoft Office Forums - View Single Post - Modify a just-pasted Excel table

p45cal · 09-25-2015, 08:51 AM

Here's hoping that I've posted in the right sub-forum - it's Word VBA in an Excel macro.
I am trying to paste multiple Excel tables into a word document which has placeholder bookmarks. This works but I want to manipulate the newly pasted tables directly after pasting.
Tables are not always inserted in the order they end up in in the Word document so identifying the newly pasted table with the likes of wdoc.tables(wdoc.tables.count) only identifies the last one.

The code I have so far is:

Code:

TableArray = Array("Table6", "Table7")
BookmarkArray = Array("BM1", "BM2")
'With GetObject("C:\Users\Public\Documents\vbaExpress53814\ReceivingDocument.docx")
Set wdoc = GetObject("C:\Users\Public\Documents\vbaExpress53814\ReceivingDocument.docx")
With wdoc
  .Parent.Visible = True
  For x = LBound(TableArray) To UBound(TableArray)
    For Each sh In Sheets
      For Each lob In sh.ListObjects
        If TableArray(x) = lob.Name Then
          'Copy Table Range from Excel
          lob.Range.Copy
          'Paste Table into MS Word (using inserted Bookmarks -> ctrl+shift+F5)
          .Bookmarks(BookmarkArray(x)).Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
          .Bookmarks(BookmarkArray(x)).Range.tables(1).autofitbehavior (2)

          'This block of comments is my trying to get the line above (autofitbehavior) to work with both pasted tables:
          'Set BMRange = .Bookmarks(BookmarkArray(x)).Range
          'BMRange.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
          '.Bookmarks.Add BookmarkArray(x), BMRange
          '.Bookmarks(BookmarkArray(x)).Range.Select  'autofitbehavior (2)

        End If
      Next lob
    Next sh
  Next x
End With  'wdoc
End Sub

It's the
.Bookmarks(BookmarkArray(x)).Range.tables(1).autof itbehavior(2)
which I can't get reliably to work; it works for one table but not for the other; it turns out that one bookmark was right at the end of a line (after some text) before the carriage return and the other was similarly placed but there was nothing else on that line bar a carriage return. Why should that matter?!

I attach an Excel workbook containing the code and the tables, and the destination Word document.

I'm trying to help someone else here:
http://www.vbaexpress.com/forum/show...Tables-To-Word
More info in msg#8 there.