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\\Documents\\Item dimensions.xlsx" "Volumes!R2C10" \a \f 5 }

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?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric FletcherCommented:
1. The link from Excel includes the ¶ from the cell(s). If you are inserting a table, use Table Properties to alter how text flows around the table. To make them disappear, you could select and set the paragraph marks as hidden text. (Note that this will only make the marks invisible when the view is toggled off with the button.) Be sure to create the link with "Link and Merge Formatting".

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).
rogerdjrAuthor Commented:

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?
Eric FletcherCommented:
Okay... I found a linked document where I'd done that. You need to use the "\r" switch to treat the link as RTF or "\t" to treat it as plain text. I would also recommend naming the source cells in your spreadsheet to make it more evident in your Word document.

So to use your example, and assuming the spreadsheet is named "BidList.xlsx" with named ranges "bidDue" and "bidLocation", your Word document would look something like this with the field code view toggled:

.... bids are due on { LINK  Excel.Sheet.12 "BidList.xlsx" bidDue \r \a } at { LINK  Excel.Sheet.12 "BidList.xlsx" bidLocation \r \a }, bidders need not be present for bid opening....
Note that if the Word link is connected to a range of cells with the \r switch, you'll get a table containing the results. If you then format the table within Word, the link keeps the cells current and won't override the Word formatting.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rogerdjrAuthor Commented:
Works perfectly - thanks a bunch
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.