Extra space when pasting link from excel to word as unformatted te

G

Guest

I'm having a problem with pasting links from excel to a word document. I'm
using Office 2003

I have a budget in Excel and I'm putting together an executive summary in
word. I want to link all of the numbers in word to the excel workbook.

For example, I want to write:
"Expenses in the Americas are xx%."
where the "xx" is a number linked from excel.

I copy the cell that has the number in excel, go to word and choose paste
special. I then select the paste as link option and choose unformatted text
from the list box.

This works fine except that in addition to the number, additional spaces are
also inserted. I can delete these, but they come back when I update the
link. So the sentence ends up looking like:
"Expenses in the Americas are xx%."

How do I get a link to just the number and not the extra spaces?

Thanks in advance for any help.
 
J

Jezebel

Press Alt-F9 to display field codes. Your link field should look something
like this:

{ LINK Excel.Sheet.8 "C:\\My Documents\\Temp\\Book1.xls" MyCell \t }

Where MyCell is the name of the cell you want (assuming you've named it --
otherwise use SheetName!CellRef). The \t switch says you want plain text,
not an image of the cell containing the value, which seems to be what you're
getting at the moment.
 
C

Cindy M -WordMVP-

Hi =?Utf-8?B?MDAxMw==?=,
This works fine except that in addition to the number, additional spaces are
also inserted. I can delete these, but they come back when I update the
link. So the sentence ends up looking like:
"Expenses in the Americas are xx%."

How do I get a link to just the number and not the extra spaces?
Click to expand...
Are you absolutely sure these spaces aren't part of the cell entry in Excel?
I've never seen anything like you describe before, if a cell doesn't contain
spaces.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
 
J

Jezebel

Are you absolutely sure these spaces aren't part of the cell entry in Excel?
I've never seen anything like you describe before, if a cell doesn't contain
spaces.
Click to expand...

Cindy, if you paste a link to a single cell, without doing anything else,
you get the entire cell itself -- the 'extra spaces' represent the width of
the cell. It becomes obvious what's happening if the cell has borders around
it in Excel.
 
C

Cindy M -WordMVP-

Hi Jezebel,
if you paste a link to a single cell, without doing anything else,
you get the entire cell itself
Click to expand...
But not if you choose to paste as unformatted (plain) text. I just
did a quick test - to make sure my memory isn't playing tricks on me
:) - and I definitely do not see any spaces when I paste as plain
text.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail :)
 
G

Guest

No, I'm definitly getting it as text. Here is the field code

{ LINK Excel.Sheet.8 "\\\\ibnbs003\\piaprojections\\2005 Budget\\Budget
Book\\2005 Excel Budget Book.xls" "Inv by Region & BU!R9C3" \a \t }
 
G

Guest

The cell contains the following formul
=ROUND(SUMPRODUCT(--(BInv_BU_rng=$B9),--(BInv_Region_rng=C$6),BInv_Amount_rng)/1000000,0)

I think it has to do with the number formatting. The numbers are currently
using a custom format of
_($_(?,??0_);_($_(??(#,##0);_($_(_?_,_?_? "-"_);_(@_)
or
_(_$_(?,??0_);_(_$_(??(#,##0);_(_$_(_?_,_?_? "-"_);_(@_)
to make sure that the first line and the total line have aligned dollar
signs and that all of the intermediate lines are aligned.

When I switch the format to "General" I don't get the spaces.

Is there a way to keep my number formatting and not get the extra spaces?
 
S

Suzanne S. Barnhill

I don't know whether it's a problem with the LINK field or not, but it's
certainly the case that EQ fields have this problem: the default space at
the end of the field code is carried over into the document; it is necessary
to remove the space to get the desired result. You might see what happens if
you use the field below as follows:

{ LINK Excel.Sheet.8 "\\\\ibnbs003\\piaprojections\\2005 Budget\\Budget
Book\\2005 Excel Budget Book.xls" "Inv by Region & BU!R9C3" \a \t}


--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
 
C

Cindy M -WordMVP-

Hi =?Utf-8?B?MDAxMw==?=,
The cell contains the following formula
=ROUND(SUMPRODUCT(--(BInv_BU_rng=$B9),--(BInv_Region_rng=C$6),BInv_Amount_rng)/1000000,0)

I think it has to do with the number formatting. The numbers are currently
using a custom format of
_($_(?,??0_);_($_(??(#,##0);_($_(_?_,_?_? "-"_);_(@_)
or
_(_$_(?,??0_);_(_$_(??(#,##0);_(_$_(_?_,_?_? "-"_);_(@_)
to make sure that the first line and the total line have aligned dollar
signs and that all of the intermediate lines are aligned.
Click to expand...
Yes, this is probably the reason for the spaces.

It looks like you can use a number switch within the Link field. After \t make sure theres
a space, then enter: \# 0
When I switch the format to "General" I don't get the spaces.

Is there a way to keep my number formatting and not get the extra spaces?
Click to expand...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply in the
newsgroup and not by e-mail :)
 
G

Guest

That works great. Thank you so much!

The only other question I have would be if you know what arguments to pass
the switch so that a decimal shows up as a percent i.e. ".57" shows up as 57%
 
C

Cindy M -WordMVP-

Hi =?Utf-8?B?MDAxMw==?=,
The only other question I have would be if you know what arguments to pass
the switch so that a decimal shows up as a percent i.e. ".57" shows up as 57%
Click to expand...
For Word, in order to do this you need to do some calculation. For example

{ = { LINK Excel.Sheet.8 "Book1" "Sheet1!R1C1" \a \t }*100 \# 0 }

Select the LINK field, then press Ctrl+F9 to put a set of field brackets around
it. Then enter the parts of the calculation.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
 
G

Guest

Works perfectly. Thank you so much.

Cindy M -WordMVP- said:
Hi =?Utf-8?B?MDAxMw==?=,

For Word, in order to do this you need to do some calculation. For example

{ = { LINK Excel.Sheet.8 "Book1" "Sheet1!R1C1" \a \t }*100 \# 0 }

Select the LINK field, then press Ctrl+F9 to put a set of field brackets around
it. Then enter the parts of the calculation.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)


This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extra spaces in a copy/paste from Excel into Word as a link with HTML formatting 0
pasting linked excel cell and maintaining formatting no matter wha 1
Extra space in links to Excel 3
pasting linked cell from Exc and maintaining formatting no matter 5
LINK field in Word - Pasting from Excel 3
Pasting from Web Pages into Word 2003 is broken 1
Pasting in Word 2007 using Track Changes 1
Paste Unformatted Text Macro Button 1

Top