A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Carry over conditional formatting



 
 
Thread Tools Display Modes
  #1  
Old May 20th, 2004, 07:26 PM
Justin
external usenet poster
 
Posts: n/a
Default Carry over conditional formatting

Hello - We have an Excel spread sheet that contains conditional formatting (red strike outs, highlighted cells with bolded text) being used as a data source. We have a Word document that has been mail merged to the above mentioned spread sheet and contains a chart displaying the data, currently with out the conditional formatting. We'd like to have the associated conditional formatting carry across from the spread sheet to the Word document during the mail merge. Is this possible in Word XP or Word 2003, if so can you please tell me how? Thanks in advance for your suggestions.
  #2  
Old May 21st, 2004, 03:26 PM
Cindy M -WordMVP-
external usenet poster
 
Posts: n/a
Default Carry over conditional formatting

Hi =?Utf-8?B?SnVzdGlu?=,

We have an Excel spread sheet that contains conditional formatting (red strike

outs, highlighted cells with bolded text) being used as a data source. We have a
Word document that has been mail merged to the above mentioned spread sheet and
contains a chart displaying the data, currently with out the conditional
formatting. We'd like to have the associated conditional formatting carry across
from the spread sheet to the Word document during the mail merge. Is this possible
in Word XP or Word 2003, if so can you please tell me how?

Between Excel and Word, no, it's not possible. But there is a way to do it, if your
table has 63 columns or less:

1. Copy the table in Excel

2. Paste into a new, empty Word document (with or without a link); the formatting
should be retained?

3. Save (and close)

4. Now select this document as the data source

5. Insert all the merge fields (you won't see the formatting, yet!)

6. Press Alt+F9 to display the field codes. Remove the term Mergefield from the
merge fields where the formatting should come across. (Note: you MUST have at least
ONE Mergefield in the merge document or you can't execute the merge)

7. Alt+F9 again, and try merging.

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 :-)

  #3  
Old May 21st, 2004, 11:11 PM
Justin
external usenet poster
 
Posts: n/a
Default Carry over conditional formatting

Cindy, Thank you for your response. I tested your steps and they worked as advertised...However, we are dealing with a table (data source) that has 73 columns of data. My next thought was to break that down into two separate data sources and try to link those to the Word doc but in reading other posts on this site I see that's not do-able. I guess my follow-up question to you (or anyone else for that matter) would be: Is the procedure you supplied for Word 2002 or for 2003? The reason I ask is that we've heard (but haven't been able to confirm) that Word 2003 can pull the conditionally formatted data directly from Excel. We don't currently have access to a copy of Word 2003 to test this and would like to confirm whether or not it can do this prior to purchasing Word 2003.

Your help so far has been wonderful and we are grateful for any additional information you can supply.
  #4  
Old May 22nd, 2004, 10:41 AM
Cindy M -WordMVP-
external usenet poster
 
Posts: n/a
Default Carry over conditional formatting

Hi =?Utf-8?B?SnVzdGlu?=,

Is the procedure you supplied for Word 2002 or for 2003? The reason I ask is that

we've heard (but haven't been able to confirm) that Word 2003 can pull the
conditionally formatted data directly from Excel. We don't currently have access to
a copy of Word 2003 to test this and would like to confirm whether or not it can do
this prior to purchasing Word 2003.

If this is true it would be big news to me. I don't see how it is possible, though,
using mail merge. The default data connection for both 2002 and 2003 is OLEDB, and
that, like an ODBC connection, links only to the data tables. DDE, the old
technology, never brought across any formatting except that applied to table content
(date, numbers, etc.).

In the hopes you had discovered something, I just tested, and I sure don't see any
conditional cell formatting come across.

Hmmm. 73 fields is so close to the "legal" number...

What I could imagine could work would be to use LINK field to bring in those "stray"
10 columns of information, although this could get tricky if there are lots of
records. LINK fields connect to the source application via OLE, which means there
could be quite a bit of overhead involved. But LINK fields do bring across
formatting.

The basic approach looks like this

{ LINK Excel.Sheet.8 "C:\\Documents and Settings\\User\\My Documents\\SalesData.xls"
"Sheet1!R{ = { MergeSeq } + 1 }C3" \a \f 4 \r }

What you're doing is telling it you want the cell at a particular row and column
intersection. The column stays the same for each record; the row index changes
(increments by one) for each record. MergeSeq does this for you, but I add 1 to it
to allow for the field names row in the Excel file.

Using this technique, you MUST merge to a new document, then Ctrl+A, F9 to force the
fields to update. At this point, you could then press Ctrl+Shift+F9 to turn the
linked information into plain, Word text (thus getting rid of the overhead, and any
danger the information could change).

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 :-)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.