Merging from Excel

[Joe Parsons]

I am using Office 2007.

I have an Excel spreadsheet that calculates several loan scenarios. The results
of the analysis are merged into a Word 2007 document.

The scenarios are presented in a matrix and copied into the Word document. I
selected the option to link to Excel and use the formatting in the destination
document.

In the same Word document, I have used MERGEFIELD plus formatting switches to
present some of the information from the spreadsheet in the text of a letter.
The source for this data is a two-row named range in the spreasheet, with the
first row being the names of the fields, the second being the actual data.

I have two problems:

First, each time I open the Word document, the Excel table appears three times.
I delete the two extra tables, but obviously I'd like not to have to do this.

Why is this happening, and how can I avoid it?

The second problem is that the merge data from the named range in the
spreadsheet doesn't update unless I close and reopen the Word document. How can
I get it to update dynamically while still giving me the ability to place the
data in-line into the text? If I paste and link the cell from the spreadsheet
into the letter, I can't merge it into the text, like this:

Your present loan is [MERGED AND FORMATTED FROM EXCEL], your rate
is [MERGED AND FORMATTED FROM EXCEL], with a payment of [MERGED AND
FORMATTED FROM EXCEL]

The end results should look like this:

Your present loan is $352,345, your rate is 7.000%, with a payment
of $2,356.

I hope I've explained my problem in an understandable way. Any help will be
greatly appreciated!

Joe Parsons

 

[Peter Jamieson]

First, each time I open the Word document, the Excel table appears three

times.
I delete the two extra tables, but obviously I'd like not to have to do
this.

OK, I tried this and it all seemed to work OK here, except that I now get
messages from Excel that I don't remember in previous versions. I'm on Word
2007 SP1. If you use Alt-F9 to view the underlying field codes, what do you
see? For example, I see

{ LINK Excel.Sheet.8 C:\\Users\\pjj.DOMAINX\\Documents\\nr.xlsx
Sheet2!R1C1:R6C4 \a \f 4 \r }

Also, you aren't doing something like pasting into an existing table?

If I paste and link the cell from the spreadsheet
into the letter, I can't merge it into the text, like this:

Yes, at best the data will refresh if you go into Word Mailings etc., open
the Edit recipients dialog box, and "refresh" the data there.

However, I don't think cell reference syntax lets you specify a cell by
using e.g. [a range name]!R2C1 - everything I have tried here so far fails.

If you can name the specific Excel cell you want to insert, and insert that
via Copy/Paste link, the text is not wrapped up in a table and should be
in-line. Whether it will update when expected or you will get error messages
from Excel I cannot say.

There are one or two other possibilities, but it looks as if these days you
have to think in terms of using VBA/VB.NET code.

--
Peter Jamieson
http://tips.pjmsn.me.uk

I am using Office 2007.

I have an Excel spreadsheet that calculates several loan scenarios. The
results
of the analysis are merged into a Word 2007 document.

The scenarios are presented in a matrix and copied into the Word document.
I
selected the option to link to Excel and use the formatting in the
destination
document.

In the same Word document, I have used MERGEFIELD plus formatting switches
to
present some of the information from the spreadsheet in the text of a
letter.
The source for this data is a two-row named range in the spreasheet, with
the
first row being the names of the fields, the second being the actual data.

I have two problems:

First, each time I open the Word document, the Excel table appears three
times.
I delete the two extra tables, but obviously I'd like not to have to do
this.

Why is this happening, and how can I avoid it?

The second problem is that the merge data from the named range in the
spreadsheet doesn't update unless I close and reopen the Word document.
How can
I get it to update dynamically while still giving me the ability to place
the
data in-line into the text? If I paste and link the cell from the
spreadsheet
into the letter, I can't merge it into the text, like this:

Your present loan is [MERGED AND FORMATTED FROM EXCEL], your rate
is [MERGED AND FORMATTED FROM EXCEL], with a payment of [MERGED AND
FORMATTED FROM EXCEL]

The end results should look like this:

Your present loan is $352,345, your rate is 7.000%, with a payment
of $2,356.

I hope I've explained my problem in an understandable way. Any help will
be
greatly appreciated!

Joe Parsons

 

[joe]

OK, I tried this and it all seemed to work OK here, except that I now get
messages from Excel that I don't remember in previous versions. I'm on Word
2007 SP1. If you use Alt-F9 to view the underlying field codes, what do you
see? For example, I see

{ LINK Excel.Sheet.8 C:\\Users\\pjj.DOMAINX\\Documents\\nr.xlsx
Sheet2!R1C1:R6C4 \a \f 4 \r }

Also, you aren't doing something like pasting into an existing table?

One of those "d'OH!" moments...I checked with Alt-f9 and discovered
that the code to link the Excel table had somehow been pasted in three
times!. I took the extra instances out and now it works fine.

If I paste and link the cell from the spreadsheet
into the letter, I can't merge it into the text, like this:

Yes, at best the data will refresh if you go into Word Mailings etc., open
the Edit recipients dialog box, and "refresh" the data there.

However, I don't think cell reference syntax lets you specify a cell by
using e.g. [a range name]!R2C1 - everything I have tried here so far fails..

If you can name the specific Excel cell you want to insert, and insert that
via Copy/Paste link, the text is not wrapped up in a table and should be
in-line. Whether it will update when expected or you will get error messages
from Excel I cannot say.

I'll try that. Thank you for the help!

Joe Parsons

 

[joe]

First, each time I open the Word document, the Excel table appears three
times.
I delete the two extra tables, but obviously I'd like not to have to do
this.

OK, I tried this and it all seemed to work OK here, except that I now get
messages from Excel that I don't remember in previous versions. I'm on Word
2007 SP1. If you use Alt-F9 to view the underlying field codes, what do you
see? For example, I see

{ LINK Excel.Sheet.8 C:\\Users\\pjj.DOMAINX\\Documents\\nr.xlsx
Sheet2!R1C1:R6C4 \a \f 4 \r }

Also, you aren't doing something like pasting into an existing table?

If I paste and link the cell from the spreadsheet
into the letter, I can't merge it into the text, like this:

Yes, at best the data will refresh if you go into Word Mailings etc., open
the Edit recipients dialog box, and "refresh" the data there.

However, I don't think cell reference syntax lets you specify a cell by
using e.g. [a range name]!R2C1 - everything I have tried here so far fails..

If you can name the specific Excel cell you want to insert, and insert that
via Copy/Paste link, the text is not wrapped up in a table and should be
in-line. Whether it will update when expected or you will get error messages
from Excel I cannot say.

When I paste the link from Excel into Word, it generates this code:
{LINK Excel.Sheet.8 "[path]" "[cell name]" \a \f5 \h \* MERGEFORMAT}

I can't find any references to the \a or \h codes, but the value
returned by the link is always on a new line.

Naming the range doesn't seem to have any effect. Is there some kind
of undocumented switch to keep it from appearing on the new line?

Thanks for the help.

Joe Parsons

 

[Peter Jamieson]

<<
{LINK Excel.Sheet.8 "[path]" "[cell name]" \a \f5 \h \* MERGEFORMAT}
Interesting that you have \*Mergeformat. Idon't, here, but Word sometimes
re-inserts it when you change certain things.

Suggestions:
a. remove \*MERGEFORMAT
b. change \f5 to \f4, and re-execute the field (you may retain some simple
formatting)
c. change \a \f4 \r to \a \t, and re-execute the filed (the formatting
will probably disappear)

Here, both results are inline. Other formats either result in images or
tables that shift the following text to a new line.

(now signing out until tomorrow at least)

--
Peter Jamieson
http://tips.pjmsn.me.uk

First, each time I open the Word document, the Excel table appears three
times.
I delete the two extra tables, but obviously I'd like not to have to do
this.

OK, I tried this and it all seemed to work OK here, except that I now get
messages from Excel that I don't remember in previous versions. I'm on
Word
2007 SP1. If you use Alt-F9 to view the underlying field codes, what do
you
see? For example, I see

{ LINK Excel.Sheet.8 C:\\Users\\pjj.DOMAINX\\Documents\\nr.xlsx
Sheet2!R1C1:R6C4 \a \f 4 \r }

Also, you aren't doing something like pasting into an existing table?

If I paste and link the cell from the spreadsheet
into the letter, I can't merge it into the text, like this:

Yes, at best the data will refresh if you go into Word Mailings etc., open
the Edit recipients dialog box, and "refresh" the data there.

However, I don't think cell reference syntax lets you specify a cell by
using e.g. [a range name]!R2C1 - everything I have tried here so far
fails.

If you can name the specific Excel cell you want to insert, and insert
that
via Copy/Paste link, the text is not wrapped up in a table and should be
in-line. Whether it will update when expected or you will get error
messages
from Excel I cannot say.

When I paste the link from Excel into Word, it generates this code:
{LINK Excel.Sheet.8 "[path]" "[cell name]" \a \f5 \h \* MERGEFORMAT}

I can't find any references to the \a or \h codes, but the value
returned by the link is always on a new line.

Naming the range doesn't seem to have any effect. Is there some kind
of undocumented switch to keep it from appearing on the new line?

Thanks for the help.

Joe Parsons

 

[jmparsons]

<<
{LINK Excel.Sheet.8 "[path]" "[cell name]" \a \f5 \h \* MERGEFORMAT}



Interesting that you have \*Mergeformat. Idon't, here, but Word sometimes
re-inserts it when you change certain things.

Suggestions:
 a. remove \*MERGEFORMAT
 b. change \f5 to \f4, and re-execute the field (you may retain some simple
formatting)
 c. change \a \f4 \r  to \a \t, and re-execute the filed (the formatting
will probably disappear)

Here, both results are inline. Other formats either result in images or
tables that shift the following text to a new line.

I tried both of those. The number still shows up on a new line. I also
tried referring to the named range. Same results.

I don't know what you mean by "re-execute the field."

Any other ideas?

Joe Parsons

 

[Peter Jamieson]

I don't know what you mean by "re-execute the field."

I mean select it and press F9 to update its value.

I test using e.g.

a{ LINK Excel.Sheet.8 "[path]" "[cell reference]" [switches] }b

The switch combinations that work here are:
1. \a \p (Inserts a picture linked to the cell)
2. \a \f4 \r (Inserts text with formatting using rtf)
3. \a \t (Inserts plain text)
4. \a \b (Inserts a bitmap image linked to the cell)
5. \a \t \u (Inserts plain unicode text)

At the moment I can't see why your system is behaving differently from mine,
but you are welcome to despam my e-mail (remove "KillmapS") and send me a
sample Excel sheet and Word doc. that show the problem.

--
Peter Jamieson
http://tips.pjmsn.me.uk

<<
{LINK Excel.Sheet.8 "[path]" "[cell name]" \a \f5 \h \* MERGEFORMAT}



Interesting that you have \*Mergeformat. Idon't, here, but Word sometimes
re-inserts it when you change certain things.

Suggestions:
a. remove \*MERGEFORMAT
b. change \f5 to \f4, and re-execute the field (you may retain some simple
formatting)
c. change \a \f4 \r to \a \t, and re-execute the filed (the formatting
will probably disappear)

Here, both results are inline. Other formats either result in images or
tables that shift the following text to a new line.

I tried both of those. The number still shows up on a new line. I also
tried referring to the named range. Same results.

I don't know what you mean by "re-execute the field."

Any other ideas?

Joe Parsons

 

[jmparsons]

I mean select it and press F9 to update its value.

Aha! That did the trick!!

I test using e.g.

a{ LINK Excel.Sheet.8 "[path]" "[cell reference]" [switches] }b

The switch combinations that work here are:
1. \a \p (Inserts a picture linked to the cell)
2. \a \f4 \r (Inserts text with formatting using rtf)
3. \a \t (Inserts plain text)
4. \a \b (Inserts a bitmap image linked to the cell)
5. \a \t \u (Inserts plain unicode text)

It is now working just the way it is supposed to. I manually edited
the LINK code to remove the MERGEFORMAT and changed the switches to \a
\t.

You are now officially my hero. I wil drink a pint of your favorite
beverage in your honor tonight.

Joe Parsons