Replace remove Links from Excel in world fields

[Peter Flindt]

Hello,
I am not sure, maybe there is another solution without VBA, but I
didn't find it.
Steps to reproduce the issue:
1) Create a new Excel file, create a merged cell with 5 columns, enter
some Text in this cell, mark this cell and press CTRL+C
2) Open a word document enter some text, add a text field, move the
cursor anywhere in this text field, choose "Edit"->"Insert
content"->"unformatted" and enable "insert link" and press Okay.
(CTRL+V not work too)

Result:
blah blah Cell_Text+5 tabs blah blah

I found no way to do this, copy the cell text as a link, without this
tabs, therefore I thought I could do this with a macro on DocumentOpen.
Here is the code snippet:
============
Private Sub Document_Open()
Dim DocumentField As Field
Dim i As Long
'Second try
For i = 1 To ActiveDocument.Fields.Count - 1
If ActiveDocument.Fields.Item(i).Type <> wdFieldLink Then
ActiveDocument.Fields.Item(i).Result.Text =
Replace(ActiveDocument.Fields.Item(i).Result.Text, Chr(9), "")
End If
Next i
'First try
'For Each DocumentField In ActiveDocument.Fields
' DocumentField.Result.Text =
Replace(DocumentField.Result.Text, Chr(9), "")
' End If
' Next DocumentField
End Sub
============
But no luck.
The Replace function find the tabs in the text and remove them, yes,
but it remove the link too. When I choose "Save document" and open it
again I have the the text, but not the actual text from the Excel
table.

In "'Second try " it starts with field count=2 one field is type =
wdFieldLink the other wdFieldFormTextInput, but the first call to
Replace removed this first item and the macro stopped, because the
second loop is outside the boundary.

Any ideas?

Peter

 

[macropod]

Hi Peter,

You could edit the link field so that it only references the upper-left cell in the source range. To do this:
.. select the linked field
.. press Shift-F9 to expose the field code it'll look something like { LINK Excel.Sheet.8 "Book1" "Sheet1!R1C1:R1C5" \a \t }
.. remove the ':R1C5' reference, so that you end up with { LINK Excel.Sheet.8 "Book1" "Sheet1!R1C1" \a \t }
.. press F9 to update the field.

 

[Peter Flindt]

macropod wrote on 16.11.2008 n Message

Hi Peter,

You could edit the link field so that it only references the upper-left cell
in the source range. To do this: . select the linked field
. press Shift-F9 to expose the field code it'll look something like { LINK
Excel.Sheet.8 "Book1" "Sheet1!R1C1:R1C5" \a \t } . remove the ':R1C5'
reference, so that you end up with { LINK Excel.Sheet.8 "Book1" "Sheet1!R1C1"
\a \t } . press F9 to update the field.

That was the trick, many thanks.

(Although I still don't understand why Word add this tabs)

Peter

 

[macropod]

Hi Peter,

The tabs were due to the fact that Word still sees each of the underlying cells and separates them with tabs.