excel link forces new line in word doc 1

[cc4mail]

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!

 

[macropod]

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]

 

[cc4mail]

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

 

[macropod]

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]

 

[cc4mail]

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

 

[macropod]

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]

 

[cc4mail]

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