We use Excel (2002, SP3) "Control Sheets" to maintain client data and link these single-cell fields into Word (2002, SP3) docuements - all running under XP-Pro on Dell Desktops.

We have used "Paste Special, Paste Link, Formatted (RTF)" and "Paste Special, Paste Link, Unformatted" and have found that Word manages to screw up the resultant formatting in different ways under both options.

We would strongly prefer to maintain the single cell formatting (usually changing text sizes, bold vs. non bold, and color) as we have it set in Excel in the resultant Word document. The result MUST look like a normal non-linked or copied text document (e.g. can't be a "worksheet object" or "picture" format) as they become final form, legal documents.

It appears that one possible work-around might be to have a macro that would re-execute all of the formatting update information contained in the paragraph ending symbol AGAIN - AFTER all of the linked fields have been automatically loaded during the document's opening process. Is this feasible? If so, would anyone out there like to contract to write such a macro? Are there other/better ways to achieve reasonable formatting consistency? (I've already suggested going to Lotus Smart Suite - - - not an option).

Thank you for your time and consideration.

Ken Snyder

If the data is linked why are you having to use paste/paste special/pase extra special?

What is happening to the format from Excel to Word or is it that you are trying to change the formatting in the Word doc in different situations?

Thanks a bunch for your thoughts, considerations, and suggestions. I'm afraid Mail Merge would be a logistical nightmare. We have @ 60 different fields each of which may be upper or lower case, depending upon specific usage. These fields are interspersed among up to close to 100 different documents - usually different from case to case, depending upon the document needs of each particular client. It looks like we'll just have to keep fighting Microsoft's inherent shortcomings.

Thanks for the reply - in answer to yours . . .
1) When the link is first established from Excel to Word, it is established via a "Paste Special, Paste Link, etc." chain of commands. This occurs only once - when the docs are first established.
2) e.g. Excel format = Times Roman, 12pt, Bold. The receiving field in Word within a paragraph (if using the "Formatted(RTF)" option is and should be the same (TR, 12pt, Bold). This works fine the first time thru - for example, with a last name of Johnson. The next time, if the name in the Excel file is changed to Smith (really, if it is changed to anything either shorter or longer than the original "Johnson", either the front part of the name or the last part of the name is, at times bolded or not bolded, underlined or not underlined, italicised or not italicized, etc.)

In other circumstances, we may have a cell in the Excel sheet that is formatted as 24pt, Bold, Italics (for cover page printing in the Word document, for exampel). This field is "copied" within the Excel document from the original 12pt field via Excel formula. The 24pt field is then "past link formatted" into a Word document to get that field as a cover page title line for example. The first time (e.g. Johnson) comes out fine in 24pt Bold. Following times (changing name in Excel from Johnson to Smith, McDonald, etc. will often (not always) yield different results.

Does that make things any clearer?

Ken

Have you tried setting this up as a mail merge? The way you are doing it it sounds like it should work ok but we're talking about MS software here! Without looking at your files I couldn't say why the formatting isn't working.

My understanding of what you are doing is:
You have a boiler plate Word doc that you have any number of placeholders that will be someone's name, and other such data - be it text or numbers. These placeholders will change for each new iteration of the Word doc so you have set up an Excel spreadsheet to enter the placeholder changes (data) to then populate the Word doc.

You can do this through a mail merge instead of using the data links. You would then merge the doc each time pointing back to the Excel file.

Give it a try and let us know if it works.

If the data is linked why are you having to use paste/paste special/pase extra special?

What is happening to the format from Excel to Word or is it that you are trying to change the formatting in the Word doc in different situations?

Have you tried setting this up as a mail merge? The way you are doing it it sounds like it should work ok but we're talking about MS software here! Without looking at your files I couldn't say why the formatting isn't working.

My understanding of what you are doing is:
You have a boiler plate Word doc that you have any number of placeholders that will be someone's name, and other such data - be it text or numbers. These placeholders will change for each new iteration of the Word doc so you have set up an Excel spreadsheet to enter the placeholder changes (data) to then populate the Word doc.

You can do this through a mail merge instead of using the data links. You would then merge the doc each time pointing back to the Excel file.

Give it a try and let us know if it works.

Thanks a bunch for your thoughts, considerations, and suggestions. I'm afraid Mail Merge would be a logistical nightmare. We have @ 60 different fields each of which may be upper or lower case, depending upon specific usage. These fields are interspersed among up to close to 100 different documents - usually different from case to case, depending upon the document needs of each particular client. It looks like we'll just have to keep fighting Microsoft's inherent shortcomings.

Thanks for the reply - in answer to yours . . .
1) When the link is first established from Excel to Word, it is established via a "Paste Special, Paste Link, etc." chain of commands. This occurs only once - when the docs are first established.
2) e.g. Excel format = Times Roman, 12pt, Bold. The receiving field in Word within a paragraph (if using the "Formatted(RTF)" option is and should be the same (TR, 12pt, Bold). This works fine the first time thru - for example, with a last name of Johnson. The next time, if the name in the Excel file is changed to Smith (really, if it is changed to anything either shorter or longer than the original "Johnson", either the front part of the name or the last part of the name is, at times bolded or not bolded, underlined or not underlined, italicised or not italicized, etc.)

In other circumstances, we may have a cell in the Excel sheet that is formatted as 24pt, Bold, Italics (for cover page printing in the Word document, for exampel). This field is "copied" within the Excel document from the original 12pt field via Excel formula. The 24pt field is then "past link formatted" into a Word document to get that field as a cover page title line for example. The first time (e.g. Johnson) comes out fine in 24pt Bold. Following times (changing name in Excel from Johnson to Smith, McDonald, etc. will often (not always) yield different results.

Does that make things any clearer?

Ken