none
Copy using Range.FormattedText winds up in wrong table cell RRS feed

  • Question

  • I am creating a template macro that will (among other things) use some cells from an existing document to populate cells of a table in the new document based on the template.  I am having problems with the following line that transfers the content of one cell in the table from the source document (as set by the tblRangeTRA range variable)  to replace the contents of a cell in the destination document table (as represented by the tblRangeRMR range variable).

    tblRangeRMR.Rows(4).Cells(2).Range.FormattedText = tblRangeTRA.Rows(3).Range.FormattedText

    When I use the .Text methods instead of .FormattedText methods, this works fine, but does not carry over the formatting from the source document cell.  I switched these over to the .FormattedText, but while it does preserve the formatting it places the resulting text at the end of Cell 1 of the specified row, wiping out the contents of Cell 2.  What appears to be happening is that the copy wipes out some end of cell markers at the end of Cell 1 causing the text to appear to be in Cell 1. 

    I have tried selecting the target range and using Selection.FormattedText but this does the same thing.  I have also tried deleting the text in Cell 2 and collapsing the range before executing the above line, but this doesn't work either.  Any assistance you might provide would be greatly appreciated.

    Friday, August 17, 2012 9:12 PM

Answers

  • Try something along the lines of:

    Dim RngSrc As Range, RngDest As Range
      With tblRangeTRA.Cell(3, 1)
        Set RngSrc = .Range
        RngSrc.End = RngSrc.End - 1
      End With
      With tblRangeRMR.Cell(4, 2)
        Set RngDest = .Range
        RngDest.End = RngDest.End - 1
        RngDest.FormattedText = RngSrc.FormattedText
      End With

    For a simple transfer, you could get awat with 'tblRangeRMR.Cell(4, 2).FormattedText = RngSrc.FormattedText' instead of the second With ... End With block, but having it facilitates any further processing you might want to do with the destination cell.

    I note too that you didn't specify which cell the source data should come from. If you have more than one cell on the source row, the result could be the insertion of multiple cells' data into the destination cell.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by Billiri Monday, August 20, 2012 1:11 PM
    Saturday, August 18, 2012 8:13 AM

All replies

  • Try something along the lines of:

    Dim RngSrc As Range, RngDest As Range
      With tblRangeTRA.Cell(3, 1)
        Set RngSrc = .Range
        RngSrc.End = RngSrc.End - 1
      End With
      With tblRangeRMR.Cell(4, 2)
        Set RngDest = .Range
        RngDest.End = RngDest.End - 1
        RngDest.FormattedText = RngSrc.FormattedText
      End With

    For a simple transfer, you could get awat with 'tblRangeRMR.Cell(4, 2).FormattedText = RngSrc.FormattedText' instead of the second With ... End With block, but having it facilitates any further processing you might want to do with the destination cell.

    I note too that you didn't specify which cell the source data should come from. If you have more than one cell on the source row, the result could be the insertion of multiple cells' data into the destination cell.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by Billiri Monday, August 20, 2012 1:11 PM
    Saturday, August 18, 2012 8:13 AM
  • Thanks.  Aside from having to modify the setting of RngDest/Src to read "With tblRangexxx.Tables(1).Cell(4,2)..." your suggestion works like a charm. 

    Regrding the omission of the cell, that was a copy and paste error which I corrected in the original post, but not until after you had replied.

    Thanks once again!

    Monday, August 20, 2012 1:11 PM