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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|