|
|
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
|
|
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.
--
Cheers
macropod
[MVP - Microsoft Word]
"Peter Flindt" <newsgroups[ at ]lastwebpage.de> wrote in message news:%23P23EIpQJHA.2228[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > 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 wrote on 16.11.2008 n Message
<#aHBw65RJHA.4772[ at ]TK2MSFTNGP06.phx.gbl> :
[Quoted Text] > 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
|
|
Hi Peter,
The tabs were due to the fact that Word still sees each of the underlying cells and separates them with tabs.
--
Cheers
macropod
[MVP - Microsoft Word]
"Peter Flindt" <newsgroups[ at ]lastwebpage.de> wrote in message news:ubAfB9MSJHA.5408[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > macropod wrote on 16.11.2008 n Message
> <#aHBw65RJHA.4772[ at ]TK2MSFTNGP06.phx.gbl> :
>> 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
>
>
|
|
|