Formatting Linked Excel Tables

[Tammie]

Formatting tables while maintaining linking is giving me
problems. Neither option of either "keep source
formatting and link to Excel" or "match destination table
style and link to Excel" seems to make a difference.
When the links are updated the tables lose formatting
that I have applied to make them fit on the page.

I have scrapped the document I'm working with and started
over fresh. I've inserted the first of several tables,
changed formatting, saved, closed, and reopened the
document. This time the table seems to have kept all the
formatting that I have applied, except the option
to "repeat as header row at the top of each page"
format.

I'm responsible to produce several documents containing
multiple linked tables forecasting growth in state energy
usage. I could sure use some pointers?

Thanks

 

[Jon Weaver]

Tammie,
To improve you control over the formatting in Word and to be able to repeat
the header rows when the table spans a second (or greater) page, I would
recommend that you choose chose Formatted Text (RTF) when you are pasting
(special) the linked Excel data; it will place the data in a table; you'll
have complete control over the formatting, which will not change when you
update the Excel workbook.

One warning -- do not change the formatting in the Excel workbook after you
have linked it. If you do change the formatting in Excel, when you update
the linked data in Word, the formatting in Word will change!

Jon

 

[Cindy M -WordMVP-]

Hi Tammie,

It sounds as if you have Word 2002 or 2003?

after you paste the table (as Jon describes) press Alt+F9 to
see the LINK field code that manages the link. If you don't
see a \* MergeFormat switch at the end of the field code,
add that in. This will tell Word to maintain (most of) the
formatting you apply to the table in the Word environment.

Formatting tables while maintaining linking is giving me
problems. Neither option of either "keep source
formatting and link to Excel" or "match destination table
style and link to Excel" seems to make a difference.
When the links are updated the tables lose formatting
that I have applied to make them fit on the page.

I have scrapped the document I'm working with and started
over fresh. I've inserted the first of several tables,
changed formatting, saved, closed, and reopened the
document. This time the table seems to have kept all the
formatting that I have applied, except the option
to "repeat as header row at the top of each page"
format.

I'm responsible to produce several documents containing
multiple linked tables forecasting growth in state energy
usage. I could sure use some pointers?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
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

 

[Tammie]

Thanks so much. A caution to anyone trying this. When I first pasted the table in RTF it didn't appear to work. The formatting was so out of whack that the data didn't even appear. However, once I highlighted the table made the formatting changes everything aligned great and the data appeared. I saved, reopened, and updated the links and all the formatting changed I made remained.

Thanks so much you have saved me

Tammie

 

[Jon Weaver]

Tammi and Cindy,

Glad the suggestions were helpful.

A warning about \*mergeformat; even though some people whose opinion I
respect (e.g., Suzanne Barnhill) say it has never failed to work, I would
recommend deleting it if Word inserts it automatically when a field is
created and not inserting it manually unless you want to take advantage of
its features *and* are aware of the potential problems associated with it.

I have had horrible experience with \*mergeformat in long documents that are
frequently revised. They are created from 30+ smaller documents by using
INCLUDETEXT fields. All revisions are done in the component documents.

All the component documents are based on the same document template with the
consistent and pervasive
use of styles; all automatic numbering is styles-based with named list
templates; In line with text wrapping style is used for graphics in the vast
majority of cases; Dave Rado's suggestions re speeding up tables are
followed, etc.; moreover, the component documents are periodically scrubbed
by tedious cutting and pasting into new documents to lessen the likelihood
of corruption.

Among these problems are:
Erratic behavior of character styles (underlining and blue font color
disappearing from part or all) and direct formatting (bold toggling off;
font colors applied to the last word in a bulleted list changing the color
of the bullet; font coloring applied to the last word in one paragraph being
applied to the last character in the next paragraph)

When the \*mergeformat switch is deleted, not one of the above problems
occurs.

Other individuals have had problems with \*mergeformat with other fields
Margaret Aldis:
http://groups.google.com/groups?hl=...geformat&meta=group%3Dmicrosoft.public.word.*

Peter Jamieson:
http://groups.google.com/groups?hl=...v=/groups?q=mergeformat+group:microsoft.publi
c.word.*%26hl%3Den%26lr%3D%26ie%3DUTF-8%26group%3Dmicrosoft.public.word.*%26
start%3D20%26sa%3DN

http://groups.google.com/groups?hl=...problems&meta=group%3Dmicrosoft.public.word.*

Jon

 

[Cindy M -WordMVP-]

Hi Jon,

A warning about \*mergeformat; even though some people whose opinion I
respect (e.g., Suzanne Barnhill) say it has never failed to work, I would
recommend deleting it if Word inserts it automatically when a field is
created and not inserting it manually unless you want to take advantage of
its features *and* are aware of the potential problems associated with it.

Absolutely. The ONLY place I ever recommend to use it is in a LINK field to
an Excel sheet, and possibly a DATABASE field. Everywhere else, it should be
deleted. It was a nice idea, but the results are so unpredictable if the
content being displayed changes... <shudder>

I lost count years ago of the number of mail merge issues resolved, simply
by removing that switch from merge fields

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
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

 

[Suzanne S. Barnhill]

FWIW, I have come to the conclusion that I am deeply ignorant about the \*
MERGEFORMAT switch and what it is supposed to do or actually does, so no one
should give any weight to my opinion at all! In some cases apparently \*
CHARFORMAT will effect the desired results, apparently, but I don't fully
understand that one, either. Which is why my documents are all carefully
handcrafted and should come with the same warning as those loathsome chunky
pieces of furniture or ratty textiles from Third World countries that come
with labels telling you that the irregularities are proof that they're
handcrafted and don't come from a large anonymous factory. FWIW, I prefer
well-made furniture produced in mills in North Carolina, with drawers that
don't stick!

 

[Cindy M -WordMVP-]

Hi Suzanne,

FWIW, I have come to the conclusion that I am deeply ignorant about the \*
MERGEFORMAT switch and what it is supposed to do or actually does, so no one
should give any weight to my opinion at all! In some cases apparently \*
CHARFORMAT will effect the desired results, apparently, but I don't fully
understand that one, either.

In a nutshell:

CharFormat will apply the direct font formatting applied to the very first
character in the field is formatted with to the result of the entire field.

MergeFormat is a bit slippery In essence, it keeps track of every bit of
direct formatting ever applied to the field result, and "puts it back" after
the field has been updated. The problem with this is, when a field updates the
content will usually change. So if characters 4 through 7, say, were formatted
bold, they'll still be bold after the update. But due to the update, the
characters in these positions perhaps shouldn't be bold; the characters that
were bold before may have moved, or been deleted entirely.

This is why it's mostly useful when applying formatting to linked table rows
or columns, although if the number of rows increases you could be in for a
nasty surprise.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
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