Change in "paste link" from Excel source in Word 2007?

[vb]

I have a number of documents that contain pasted links to lengthy text
strings in Excel. First I'll give a thumbnail of the overall process, then
explain my issue.

We conduct a lot of surveys and keep the master data files in SPSS. I have
created processes that aggregate the data in SPSS and then dump the results
out into Excel. Since the save to Excel (at least at the time I wrote it)
always overwrites the existing file, I have a second spreadsheet read the raw
data from the first and perform certain corrections, resulting in a document
I can mailmerge into a template to automatically create tables. I have been
using Windows 2003 compatibility mode for these files because then I can use
DDE, and I am using DDE because then the source formatting I apply in Excel
is carried over to Word.

For open-ended comments, I bring those into Excel, then append them one
after the other with a hard return (char 13) in-between. The result used to
be that I could paste a link to a single cell in the Excel spreadsheet, and
when it updated all of the comments would appear as a single, bulleted list.

All of the links that I put in place using Word 2003 continue to work in
this fashion. I attempted to add another link for a new field now after I
have updated to Word 2007, and it's not working the same. The link now is
pasted within a set of quotation marks. When I apply bullet formatting, it
will only apply the bullet to the first line of text; even though each item
appears on a separate line as before, something about the way it's pasted
doesn't allow me to apply formatting to it, even though it was inserted as
Paste Special>Unformatted Text.

I've played around with the Word options. I turned off smart quotes, I
turned off smart cursoring, I've turned off separate paragraph and page
breaks. Nevertheless, it keeps pasting in quotes.

I've read about a "paste text only" option, but even though I have "show
paste options buttons" turned on, I have never seen this option come up.

How can I get the link to work the way it did in Word 2003?

 

[Herb Tyson [MVP]]

It sounds like a manual line break character is being pasted instead of the
original chr$(13). This causes Word to treat that pasted bit as a single
paragraph, which is why you get only the one bullet.

Here, when I use Edit/Paste Special/Unformatted text, I do get the original
^13 characters instead of manual line breaks (^11). If that's not working
right for you, and if you're using a program to insert the ^13 characters,
you might try using a ^13 ^10 (carriage return, line feed) combination,
instead.

Or... if you're not otherwise deliberately using manual line breaks, you
could change them into regular paragraph breaks using Find/Replace: replace
^11 with ^13.

 

[vb]

I don't know why this field would require me to embed chr(13) and chr (10)
when all of the other fields worked fine just using chr(13). The other thing
of course is that it appears that the inability to format is tied to the fact
that the result is being pasted as a quote.

I dug up a laptop that was still running Word 2003 and to my dismay that now
also pasted the cell contents as a quoted string, so the fact that I opened
up these documents in Office 2007 and saved them in compatibility mode had
something to do with the problem.

Finally, I ended up bringing up the field codes, copying the reference from
the cell next to it, and manually adjusting the cell reference. Finally the
link worked the way I wanted it to.

To see if I could learn anything, I then repasted the link as I had been
doing and compared the field codes from the results. Both of them had the
field switch \a, but the old version had the switched \F 4 and \r, whereas
the new link had the field switch \t. The description of the \t switch sounds
like it may be responsible for treating the referent contents as a quote. So
apparently the problem is related to different switched being applied when
pasting special>link>unformatted text. I haven't seen anything in the option
menu that would allow you to override this behavior...