×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

excel link forces new line in word doc

excel link forces new line in word doc

excel link forces new line in word doc

(OP)
I have a simple link from excel to a word doc. (100 price fields in excel to 100 linked fields in the word doc.

When the link updates, the linked field in the word doc is forced to the next line. <backspace> will put the field back up on the correct line, but removes destination formatting. It is not a margin issue, there is plenty of room. 100 fields is a lot to correct by hand.

Any idea as to why the link field is being forced to the next line?

Thanks!

RE: excel link forces new line in word doc

Hi cc4mail,

Which link format are you using? They don't all behave the same way.

If you're pasting the data as formatted text, unformatted text or unicode text, I'd only expect to see an extra line if there was one in the Excel cell.

The hyperlink and HTML formats both insert an extra paragraph, which you can't delete without deleting the pasted object.

All the other formats paste as floating objects, which could cause other elements on the page to shuffle around but the paragraph metaphor doesn't really apply to these.

Cheers

[MS MVP - Word]

RE: excel link forces new line in word doc

(OP)
I am using Excel, Word 2002.

I am using the MS excel worksheet object, (item=data!R..C..), with auto updates and preserved formatting.

I don't think arial unicode is installed. Excel source formatting (cell) is number, 3 decimal places. I tried currency and general with no success.

This was just a simple copy, paste, format operation until the line breaks showed up. There is a small formatting marker after the linked field that I don't know how to modify or change.

Perhaps my whole approach to this is wrong? (linking prices in a worksheet to a form letter)

Does this require a linked field in Word with something like a string function e.g. str(data!R..C..),1,6) to limit the copied characters inserted to the Word field?

As you can tell, I am not an excel guy, but i really appreciate your help.

cc
 

RE: excel link forces new line in word doc

Hi cc4mail,

The paste format references I referred to relate to how the data are pasted into Word, not the formatting in Excel.

The Excel formatting might only have mattered if there was a line-feed in the cell contents, which apparently there is not.

So, how was the link set up in Word?

Cheers

[MS MVP - Word]

RE: excel link forces new line in word doc

(OP)
I originally copied the cell from excel, pasted into the word doc and formatted it with a link to excel and destination formatting.

I also tried Paste Special, with a link to excel, and unformatted text.

The first option breaks to a new line on updating, the second loses font formatting, but stays on the correct line. Setting the font and size in the word field didn't help. It was lost on the update.  

Neither maintain relative referencing, eg, insert a line in excel, and goodbye to the link.

Seems a simple issue is becoming complicated. Perhaps using vlookup to find the part number in excel, then linking to the price cell with the vlookup results is more appropriate? This seems like a basic workflow process, made difficult by Microoft, as usual.  

format:

part#   description                     price
700114  big part with little bolts
        red, in cardboard box
        shipped ground..................$3.076

thanks for your help, this is my problem, but I'm lost.

cc
 

RE: excel link forces new line in word doc

Hi cc,

If you want the linked field to mainatin your preferred formatting, add a Charformat switch to the field. To do this:
 . select the field in Word
 . press Shift-F9 to expose the code, which will look vaguely like -
{LINK Excel.Sheet.8 "C:\\My Documents\\ExcelFile.xls" "Sheet1!R22C1" \a \t}
 . add the charformat switch, thus -
{LINK Excel.Sheet.8 "C:\\My Documents\\ExcelFile.xls" "Sheet1!R22C1" \a \t \* Charformat}
 . Format the 'L' in 'Link' with whatever attributes you want
 . Press F9 to update the field
If you want, you can even add a numeric picture switch (before the Charformat switch) to format a number differently than how it's displayed in Excel. See Word's help file for more details on field switches.

To maintain the relative referencing, give the source cell a defined name in Excel, then modify the reference in Word's link field to point to that name instead of the sheet/cell address. This way, if you insert a new row/column before the named range, the address change for the named cell won't affect the link. Continuing with the above example, if you name the source cell 'Price' in Excel, you'd change the Word field code to -
{LINK Excel.Sheet.8 "C:\\My Documents\\ExcelFile.xls" Price \a \t \* Charformat}

Cheers

[MS MVP - Word]

RE: excel link forces new line in word doc

(OP)
Thanks Macropod!

That's what I needed. I wouldn't have known where to look for charformat switch. That made it simple. If Microsoft could just write it that way!

I appreciate the help, and the "cheers" encouraged me to have a margarita.

Thanks for your efforts!

cc

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close