Automatically Inserting Data in Word File - Forcing Hard Returns with the Link
I am back to work on my project and I am using the suggestion to "Use the LINK field code. "
The easy way is to copy the cell(s) you want within Excel, then use Paste Special in Word to choose the "Keep and Link Source Formatting" or "Link and Merge Formatting" depending on how you want to manage the formatting. You can also paste it in as a Picture if you have no need for it to change dynamically. (These are options available from Paste in Word 2010; I can't recall if they are available in Word 2007, but the end results for the first two are the same if you use the LINK field code.)
Here's what the field code for a dynamic link might look like (use Alt-F9 to toggle the view between result and code):
{ LINK Excel.Sheet.12 "C:\\Users\\Eric\\Document
In Word, the above displays the calculated result from cell J2 (row 2, column 10) within within the sheet named "Volumes" in my "Item dimensions.xlsx" Excel 12 spreadsheet (Office 2010). The "\a" switch updates the field code automatically; the "\f 5" switch causes it to match the destination formatting (i.e. what I use in Word).
It is working splendidly, but has me baffled with one nuance. Each field entry forces a hard return before and after the inserted link. How can I stop this?
Also, the link field shows the full path - can I get it to just show the relative path so that the word document is linked to a spreadsheet in its own path and when I copy the whole batch to another directory the links will adjust?
Thanks
The easy way is to copy the cell(s) you want within Excel, then use Paste Special in Word to choose the "Keep and Link Source Formatting" or "Link and Merge Formatting" depending on how you want to manage the formatting. You can also paste it in as a Picture if you have no need for it to change dynamically. (These are options available from Paste in Word 2010; I can't recall if they are available in Word 2007, but the end results for the first two are the same if you use the LINK field code.)
Here's what the field code for a dynamic link might look like (use Alt-F9 to toggle the view between result and code):
{ LINK Excel.Sheet.12 "C:\\Users\\Eric\\Document
In Word, the above displays the calculated result from cell J2 (row 2, column 10) within within the sheet named "Volumes" in my "Item dimensions.xlsx" Excel 12 spreadsheet (Office 2010). The "\a" switch updates the field code automatically; the "\f 5" switch causes it to match the destination formatting (i.e. what I use in Word).
It is working splendidly, but has me baffled with one nuance. Each field entry forces a hard return before and after the inserted link. How can I stop this?
Also, the link field shows the full path - can I get it to just show the relative path so that the word document is linked to a spreadsheet in its own path and when I copy the whole batch to another directory the links will adjust?
Thanks
ASKER
Hmmm
I want the paragraph to read .... bids are due on 12/21/2011 at 2340 Garden Road, bidders need not be present for bid opening....
'12/21/2011' being one input from excel and '2340 Garden Road' the other input from excell. This would be part of a larger text parahraph. Is the link method the wrong way to do this without adding paragraph marks?
I want the paragraph to read .... bids are due on 12/21/2011 at 2340 Garden Road, bidders need not be present for bid opening....
'12/21/2011' being one input from excel and '2340 Garden Road' the other input from excell. This would be part of a larger text parahraph. Is the link method the wrong way to do this without adding paragraph marks?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works perfectly - thanks a bunch
2. You can edit out the common part of a path within a field code. If you have a number of them, use Alt-F9 to see the codes, then use Find and Replace to change the path for all at once.
Learn more about this topic from this Microsoft page -- and in particular read how to set a "hyperlink base" within a Word document. This lets you define the part of a path shared by the Word file containing the links and the source file(s).