+ Reply to Thread
Results 1 to 21 of 21

Link Excel 2013 to Word 2013

  1. #1
    Registered User
    Join Date
    11-08-2012
    Location
    tucson,arizona
    MS-Off Ver
    Excel 2013
    Posts
    13

    Link Excel 2013 to Word 2013

    I have this table setup in a Excel 2013 Spreadsheet and I am trying to link multiple items in each row to a Word 2013 Document template I've created. What I want to happen is when a new row of data is entered it will be reflected in the word document when opened. Hope this make sense. Any suggestions or help would be most appreciated. I have attached a screenshot that I hope helps.
    Attached Images Attached Images

  2. #2
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Link Excel 2013 to Word 2013

    If you name the Excel range, then copy & paste the named range into Word using Paste Special with the Paste Link option, Word should use the range name in the link and, when the area spanned by the named range changes in Excel, that change should be reflected in Word.

    See also:
    http://answers.microsoft.com/en-us/o...9-127d2c8224df
    http://www.mrexcel.com/forum/general...ml#post1652970
    Last edited by macropod; 02-08-2013 at 08:12 PM.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    11-08-2012
    Location
    tucson,arizona
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Link Excel 2013 to Word 2013

    Using the named range option works fine, but the problem is when the word document opens up its reflecting all the cells in the named range and I want it to only reflect the only the most recent cell added to that column. Hope that makes better sense. Let me know if I need to further clarify or include a screenshot. Thanks for all your help.

  4. #4
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Link Excel 2013 to Word 2013

    In that case, you'll need to make the named range refer to only the cell(s) of interest.

  5. #5
    Registered User
    Join Date
    11-08-2012
    Location
    tucson,arizona
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Link Excel 2013 to Word 2013

    The name range would be referring to only one cell per column at a time. The end goal is when the word document opens up every time, I want it to reference the next row in the same column and replace the previous row. Does this make sense?

  6. #6
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Link Excel 2013 to Word 2013

    As I said, changing the named range in Excel will achieve that. Anything more automated would require a macro.
    The end goal is when the word document opens up every time, I want it to reference the next row in the same column and replace the previous row
    I doubt you'd want Word to go to the next row if it has no data (eg because no new data have been added since the last time the document was opened).

  7. #7
    Registered User
    Join Date
    11-08-2012
    Location
    tucson,arizona
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Link Excel 2013 to Word 2013

    I apologize, I'm probably not explaining this properly. Let me try and get a better screen shot together that would help better explain what I am trying to accomplish. I did forget to mention that I am working with this excel table I've created and there is a hyperlink at the end of each row that opens up a word document template. So when new information is added to a new row and that hyperlink is clicked on it opens up a word document reflecting the information in that row.

  8. #8
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Link Excel 2013 to Word 2013

    From what you've described, it seems you'll need a macro like the following to create & populate the new Word document:
    Please Login or Register  to view this content.
    The above sub would be placed in the relevant worksheet's code module. You would, of course, need to supply the correct template, table & cell referencing details.

  9. #9
    Registered User
    Join Date
    11-08-2012
    Location
    tucson,arizona
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Link Excel 2013 to Word 2013

    Thank you very much. I will try that and see if that works. Just had a couple questions regarding the macro. In the code, where do I need to supply the table and cell refercences? I'm not too familar with VB.

  10. #10
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Link Excel 2013 to Word 2013

    In the code I posted, the table & row references are provided by:
    wdDoc.Tables(1).Rows.Last
    This says to find the last row in the first table. The For ... Next loop simply goes through all the cells on the row, populating each cell with the contents of the corresponding cell in Excel.

    Much more sophistication is possible. The posted code is just a demonstration of the basics.

  11. #11
    Registered User
    Join Date
    11-08-2012
    Location
    tucson,arizona
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Link Excel 2013 to Word 2013

    The problem I am having now is I'm getting the "Compile Error: Expected End Sub" error message. How do I fix this? I will attach a screenshot to better help.
    Attached Images Attached Images
    Last edited by drosen99; 02-21-2013 at 06:06 PM.

  12. #12
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Link Excel 2013 to Word 2013

    The highlighted line is for an 'exceltoword' sub that has nothing to do with the code I posted. It appears to be part of a sub you started working on, or one that's been overwritten with the code I posted.

  13. #13
    Registered User
    Join Date
    11-08-2012
    Location
    tucson,arizona
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Link Excel 2013 to Word 2013

    I was able to enter the macro and everything, but when I go to run it I am getting the "The requested member of the collection does not exist." What does that mean? and what do I need to do to fix this? I will include an attachment to show whats going on.
    Attached Images Attached Images

  14. #14
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Link Excel 2013 to Word 2013

    I'm not sure why you're getting that particular error message. It's as if the code is ignoring the 'If Not .Cells(iCol) Is Nothing Then' condition. Try the following revision:
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    11-08-2012
    Location
    tucson,arizona
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Link Excel 2013 to Word 2013

    Okay that macro code worked and the template I've created opened up in Microsoft Word, but how do I then have the information in the "last row" from my excel table be populated into the word template when it opens up? Hope that makes sense.

  16. #16
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Link Excel 2013 to Word 2013

    As coded, the macro pulls the data from whatever row the Active Cell is on. To pull the data from the last row, use:
    iRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

  17. #17
    Registered User
    Join Date
    11-08-2012
    Location
    tucson,arizona
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Link Excel 2013 to Word 2013

    This work, thanks. Now another issue I just realized I am running into is that I actually I have three different templates that the end user can choose from at the end of the row. Also, when the template opens up how do I make sure the information from the "last active row" is populated into the template? Do I use paste special links between excel and word? Thank you for all your help, I really do appreciate it.

  18. #18
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Link Excel 2013 to Word 2013

    Quote Originally Posted by drosen99 View Post
    I actually I have three different templates that the end user can choose from at the end of the row.
    How is that choice made (eg does the hyperlink cell they click on determine which template should be used, or should the option to choose a template be coded into the macro)? Obviously, as coded, the macro only opens a single, predefined template, extra code will be required to provide for different templates and, if the the hyperlink cell they click on determines which template should be used, you'll need rules about which cell relates to what template.

    To give the user a choice, you could use code like:
    Please Login or Register  to view this content.
    where each template's name is input into the StrTmplts variable. The code is flexible enough to allow at least 20 templates.
    Also, when the template opens up how do I make sure the information from the "last active row" is populated into the template?
    Using 'iRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row', the macro already does that.
    Do I use paste special links between excel and word?
    Not unless you want to establish a permanent link between the document and the cells. Doing so would cause the document content to change if you later changed the cell content, but could result in errors if the document is moved to a different PC or the workbook is renamed or moved to a different folder.

  19. #19
    Registered User
    Join Date
    11-08-2012
    Location
    tucson,arizona
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Link Excel 2013 to Word 2013

    To answer your question the hyperlink cell is link to another cell that has a dropdown menu with four different templates to choose from. Sorry if I made it real confusing. Also, I am having trouble locating where I need to put the template's name in the code??
    Last edited by drosen99; 02-26-2013 at 02:01 PM. Reason: Forgot to add something

  20. #20
    Registered User
    Join Date
    11-08-2012
    Location
    tucson,arizona
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Link Excel 2013 to Word 2013

    The other problem I just noticed is that when the template is opening up it's just referencing the same cell in that column, not the last cell in that particular column??

  21. #21
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Link Excel 2013 to Word 2013

    Quote Originally Posted by drosen99 View Post
    To answer your question the hyperlink cell is link to another cell that has a dropdown menu with four different templates to choose from. Sorry if I made it real confusing. Also, I am having trouble locating where I need to put the template's name in the code??
    I think you'll need to post a workbook with some sample data (including the dropdown menu) before I can do any more on that front. I suspect it's going to take quite a bit more work to integrate the processes.
    Re: Link Excel 2013 to Word 2013
    The other problem I just noticed is that when the template is opening up it's just referencing the same cell in that column, not the last cell in that particular column??
    Because you didn't give any indication of how the code is supposed to determine the start & end columns, I simply coded the macro to start and column 1 an continue processing until it reached the last column in the table or the worksheet as a whole, whichever came first. As I said in post #10:
    The For ... Next loop simply goes through all the cells on the row, populating each cell with the contents of the corresponding cell in Excel.

    Much more sophistication is possible. The posted code is just a demonstration of the basics.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1