#  Other Applications & Softwares  > Word Formatting & General >  >  Link Excel 2013 to Word 2013

## drosen99

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.

----------


## macropod

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

----------


## drosen99

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.

----------


## macropod

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

----------


## drosen99

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?

----------


## macropod

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).

----------


## drosen99

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.

----------


## macropod

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.

----------


## drosen99

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.

----------


## macropod

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.

----------


## drosen99

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.

----------


## macropod

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.

----------


## drosen99

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.

----------


## macropod

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.
```

----------


## drosen99

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.

----------


## macropod

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

----------


## drosen99

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.

----------


## macropod

> 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.

----------


## drosen99

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??

----------


## drosen99

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??

----------


## macropod

> 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.

----------

