locked
Paste special with multiple cells RRS feed

  • Question

  • I have an Excel 2010 document that contains many data in many tables. I also have a Word 2010 document that contains empty table shells. What I'd like to do is link the Word document to the Excel document so that the Word document updates when the Excel document is overwritten with new data.

    The problem I'm having is that when I highlight multiple cells in Excel, and then highlight the same number of cells in Word, and then paste special in the data, ALL of the cells from Excel get pasted into the upper left cell in Word. Strangely, this hasn't been a problem with normal pasting. Only the special pasting.

    I can special paste one cell at a time, but in my case that's thousands of cells and not worth my time if there's a better way. So, is there a way to paste special multiple cells from Excel into a multiple cells in a Word table?

    Thanks for the help!

    Sunday, November 19, 2017 12:00 AM

All replies

  • You'd do better to construct the entire table in Excel, then paste that using Paste Special with the 'paste link' option and the paste format of your choice; otherwise you do have to do each cell individually and that, in turn, will impose a severe performance hit with updating the links.

    Hint: If the Excel range is liable to expand/contract, name that range before doing the copy paste. That way, the linked content in Word will reference that named range, allowing it to expand/contract to match changes in the named range in Excel.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Sunday, November 19, 2017 3:50 AM
  • Paul,

    I appreciate the response. Thank you!

    Unfortunately, I'm not sure constructing the entire table in Excel is an option for me. We use a software program (SAS) that we've coded to create it's output in Excel format. So we're kind of at the mercy of whatever the output comes out as. The good news is that I've also coded it so that the information is located in the same exact cells each time the program is run, even if there is no data in a particular cell.

    I will try and see if I can work with pasting a table at a time, and formatting it in Word after. But it does absolutely help in knowing that I can't paste multiple cells like I had wanted to. Do you know if there is a limit on how many cells should be linked? I'm talking about approximately 33,000 cells, so maybe this function just isn't meant for that amount of data.

    I'm also a little disappointed in Microsoft's help options, since after an entire week of exchanging messages on their official Twitter account and calling their customer service number, not a single person was able to give me the simple answer of "No, sorry, that isn't possible."

    Thank again,

    Nick



    • Edited by NickKolu82 Sunday, November 19, 2017 4:38 PM
    Sunday, November 19, 2017 3:43 PM
  • External links use fields. The maximum number of fields Word 2007 & later support is 2,147,483,647. Having more than a few dozen links to external files is liable to severely hamper performance when opening the document and/or refreshing its links. Having anything like 33,000 links would be intolerably slow.

    Since your SAS output is always to the same cells, I imagine you could prepare those cells and their neighbours with both the formatting and any boilerplate content you require for a single table you could link to Word in each case. If not, a macro could be used to do so once the SAS data are output to the workbook.


    Cheers
    Paul Edstein
    [MS MVP - Word]


    Sunday, November 19, 2017 8:23 PM
  • Hi Nick,

    I am writing to see how everything is going with this issue. If the above reply by Paul is helpful, could you please help mark it as answer so that others who might have a similar question can more easily find the useful one? Thanks for your understanding and support.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Tuesday, November 21, 2017 9:50 AM