Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-25-2015, 08:51 AM
p45cal p45cal is offline Modify a just-pasted Excel table Windows 7 32bit Modify a just-pasted Excel table Office 2010 32bit
Expert
Modify a just-pasted Excel table
 
Join Date: Apr 2014
Posts: 514
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default Modify a just-pasted Excel table

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.
Attached Files
File Type: xlsm Table Transfer.xlsm (22.1 KB, 5 views)
File Type: docx ReceivingDocument.docx (14.2 KB, 5 views)
Reply With Quote
  #2  
Old 09-25-2015, 04:56 PM
macropod's Avatar
macropod macropod is offline Modify a just-pasted Excel table Windows 7 64bit Modify a just-pasted Excel table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Change:
Code:
          '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)
to:
Code:
         'Paste Table into MS Word & format
        With myDoc.Bookmarks(BookmarkArray(x)).Range
          .PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
          .MoveEnd wdTable, 1
          .Tables(1).AutoFitBehavior (wdAutoFitWindow)
        End With
PS: When posting the code at VBAX, please credit this site with the above and provide a link to this thread.
PPS: snb has a long-established reputation, both a VBAX and elsewhere, for posting code that doesn't actually address the user's needs.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-26-2015, 01:02 PM
p45cal p45cal is offline Modify a just-pasted Excel table Windows 7 32bit Modify a just-pasted Excel table Office 2010 32bit
Expert
Modify a just-pasted Excel table
 
Join Date: Apr 2014
Posts: 514
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

Thank you Paul, it works and solution offered at VBAX with attendant references.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Modify a just-pasted Excel table How do you modify the font of a clickable table of contents dt123 Word 4 05-01-2014 08:47 AM
Modify a just-pasted Excel table Heading row disappears from table styles when pasted table is selected andrewballem Word Tables 2 11-12-2013 05:18 AM
connectors between cells in pasted Excel table eNGiNe Word 7 10-16-2013 11:58 PM
Can you modify the table of authorities category title? New Daddy Word 0 11-05-2012 12:11 PM
Modify a just-pasted Excel table Picture to not modify table remodel Word Tables 1 10-05-2012 09:59 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:44 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft