Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automated Mail Merge With Excel Sheet containing data.
I have an automated mail merge program that accepts data from different
systems. The data is in an Excel Sheet. Some of the data has blanks in the data. The data would look something like this: " DataField1" " DataFields2" etc. I need to keep the leading blanks so the data will line up on the merged document. I have tried enclosing the data in "" in the Excel Cell. This keeps the blanks, but the quotes print out on the document. Any help would be appreciated. Thanks |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automated Mail Merge With Excel Sheet containing data.
If you want to right-align all these texts you would probably be better off
putting a right-aligned tab in Word immediately before the appropriate merge field. [Otherwise, unfortunately all the connection methods that Word can use to get data from Excel strip off both leading and trailing blanks, except the Excel converter, which is no longer provided with Word (since version 2002 or 2003, I forget which) or in the add-on converter pack. If you really want, I think you can get a copy of the converter from Graham Mayor's download page at http://www.gmayor.com/downloads.htm . Be aware that the converter has been withdrawn for a reason, has not been maintained for some time, and will always pop up a dialog whenever you connect using it] Peter Jamieson "ksg" wrote in message ... I have an automated mail merge program that accepts data from different systems. The data is in an Excel Sheet. Some of the data has blanks in the data. The data would look something like this: " DataField1" " DataFields2" etc. I need to keep the leading blanks so the data will line up on the merged document. I have tried enclosing the data in "" in the Excel Cell. This keeps the blanks, but the quotes print out on the document. Any help would be appreciated. Thanks |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automated Mail Merge With Excel Sheet containing data.
Thanks for the reply. Unfortunately I can not right justify and have the
data look right. Here is an example of the data: 907__BASIC MET PROF 518__CBC W/DIFF 106__AFP 1600__COMP MET PROF 520__CBC W/O DIFF 428__CEA 915__LIPID PROF 546__PLATELET CT 210__CA 19-9 1601__HEP FUNC PROF 276__IRON PROF Each line is a formatted line of text that I am treating as a merge field. There is a lot of this data and there is no way to reliably parse the data into individual fields. When I merge the delimited file manually using Word it works fine with quotes, but using the program the merge document prints with the quotes. If you have any other ideas, please let me know. Thanks "Peter Jamieson" wrote: If you want to right-align all these texts you would probably be better off putting a right-aligned tab in Word immediately before the appropriate merge field. [Otherwise, unfortunately all the connection methods that Word can use to get data from Excel strip off both leading and trailing blanks, except the Excel converter, which is no longer provided with Word (since version 2002 or 2003, I forget which) or in the add-on converter pack. If you really want, I think you can get a copy of the converter from Graham Mayor's download page at http://www.gmayor.com/downloads.htm . Be aware that the converter has been withdrawn for a reason, has not been maintained for some time, and will always pop up a dialog whenever you connect using it] Peter Jamieson "ksg" wrote in message ... I have an automated mail merge program that accepts data from different systems. The data is in an Excel Sheet. Some of the data has blanks in the data. The data would look something like this: " DataField1" " DataFields2" etc. I need to keep the leading blanks so the data will line up on the merged document. I have tried enclosing the data in "" in the Excel Cell. This keeps the blanks, but the quotes print out on the document. Any help would be appreciated. Thanks |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automated Mail Merge With Excel Sheet containing data.
The simplest approach is probably to use quotes, or preferably a character
sequence that you know never appears in the data, merge to an output document, then use VBA (say) to do a global find/replace to remove the quotes or character sequence. Other than that, I think you would have to merge with another format. However, a. the only one that I know (off the top of my head) will preserve the spaces is to use a Word document with a table as the data source, and that has a limit of 63 or 64 columns. To try it, you can copy/paste your Excel sheet into a Word document, using Word Edit|"Paste Special" and selecting Formatted Text (RTF) as the format. b. you would either have to automate Excel and Word to copy/paste (somehow or other) the data into Word, or maybe you could start with whatever source you are using to populate your Excel sheet. However, in that case, the question of how to get the data into Word while preserving the spaces does not go away easily. Peter Jamieson "ksg" wrote in message ... Thanks for the reply. Unfortunately I can not right justify and have the data look right. Here is an example of the data: 907__BASIC MET PROF 518__CBC W/DIFF 106__AFP 1600__COMP MET PROF 520__CBC W/O DIFF 428__CEA 915__LIPID PROF 546__PLATELET CT 210__CA 19-9 1601__HEP FUNC PROF 276__IRON PROF Each line is a formatted line of text that I am treating as a merge field. There is a lot of this data and there is no way to reliably parse the data into individual fields. When I merge the delimited file manually using Word it works fine with quotes, but using the program the merge document prints with the quotes. If you have any other ideas, please let me know. Thanks "Peter Jamieson" wrote: If you want to right-align all these texts you would probably be better off putting a right-aligned tab in Word immediately before the appropriate merge field. [Otherwise, unfortunately all the connection methods that Word can use to get data from Excel strip off both leading and trailing blanks, except the Excel converter, which is no longer provided with Word (since version 2002 or 2003, I forget which) or in the add-on converter pack. If you really want, I think you can get a copy of the converter from Graham Mayor's download page at http://www.gmayor.com/downloads.htm . Be aware that the converter has been withdrawn for a reason, has not been maintained for some time, and will always pop up a dialog whenever you connect using it] Peter Jamieson "ksg" wrote in message ... I have an automated mail merge program that accepts data from different systems. The data is in an Excel Sheet. Some of the data has blanks in the data. The data would look something like this: " DataField1" " DataFields2" etc. I need to keep the leading blanks so the data will line up on the merged document. I have tried enclosing the data in "" in the Excel Cell. This keeps the blanks, but the quotes print out on the document. Any help would be appreciated. Thanks |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automated Mail Merge With Excel Sheet containing data.
Thanks Peter.
I am already merging to a new document to accommodate the printing of multiple copies so I think I will try the Global Change. Do you have an example of the Global Change function? Again thanks for your help. Your help is invaluable for those of us who don't do this type of coding everyday. "Peter Jamieson" wrote: The simplest approach is probably to use quotes, or preferably a character sequence that you know never appears in the data, merge to an output document, then use VBA (say) to do a global find/replace to remove the quotes or character sequence. Other than that, I think you would have to merge with another format. However, a. the only one that I know (off the top of my head) will preserve the spaces is to use a Word document with a table as the data source, and that has a limit of 63 or 64 columns. To try it, you can copy/paste your Excel sheet into a Word document, using Word Edit|"Paste Special" and selecting Formatted Text (RTF) as the format. b. you would either have to automate Excel and Word to copy/paste (somehow or other) the data into Word, or maybe you could start with whatever source you are using to populate your Excel sheet. However, in that case, the question of how to get the data into Word while preserving the spaces does not go away easily. Peter Jamieson "ksg" wrote in message ... Thanks for the reply. Unfortunately I can not right justify and have the data look right. Here is an example of the data: 907__BASIC MET PROF 518__CBC W/DIFF 106__AFP 1600__COMP MET PROF 520__CBC W/O DIFF 428__CEA 915__LIPID PROF 546__PLATELET CT 210__CA 19-9 1601__HEP FUNC PROF 276__IRON PROF Each line is a formatted line of text that I am treating as a merge field. There is a lot of this data and there is no way to reliably parse the data into individual fields. When I merge the delimited file manually using Word it works fine with quotes, but using the program the merge document prints with the quotes. If you have any other ideas, please let me know. Thanks "Peter Jamieson" wrote: If you want to right-align all these texts you would probably be better off putting a right-aligned tab in Word immediately before the appropriate merge field. [Otherwise, unfortunately all the connection methods that Word can use to get data from Excel strip off both leading and trailing blanks, except the Excel converter, which is no longer provided with Word (since version 2002 or 2003, I forget which) or in the add-on converter pack. If you really want, I think you can get a copy of the converter from Graham Mayor's download page at http://www.gmayor.com/downloads.htm . Be aware that the converter has been withdrawn for a reason, has not been maintained for some time, and will always pop up a dialog whenever you connect using it] Peter Jamieson "ksg" wrote in message ... I have an automated mail merge program that accepts data from different systems. The data is in an Excel Sheet. Some of the data has blanks in the data. The data would look something like this: " DataField1" " DataFields2" etc. I need to keep the leading blanks so the data will line up on the merged document. I have tried enclosing the data in "" in the Excel Cell. This keeps the blanks, but the quotes print out on the document. Any help would be appreciated. Thanks |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automated Mail Merge With Excel Sheet containing data.
For example, when the new document has been created and is the active
document (and it usually is immediately after the merge, something like With ActiveDocument.Content.Find .ClearFormatting .Replacement.ClearFormatting .Text = "the text you are looking for" .Replacement.Text = "" .Forward = True .Wrap = wdFindContinue .Execute Replace:=wdReplaceAll End With Peter Jamieson "ksg" wrote in message ... Thanks Peter. I am already merging to a new document to accommodate the printing of multiple copies so I think I will try the Global Change. Do you have an example of the Global Change function? Again thanks for your help. Your help is invaluable for those of us who don't do this type of coding everyday. "Peter Jamieson" wrote: The simplest approach is probably to use quotes, or preferably a character sequence that you know never appears in the data, merge to an output document, then use VBA (say) to do a global find/replace to remove the quotes or character sequence. Other than that, I think you would have to merge with another format. However, a. the only one that I know (off the top of my head) will preserve the spaces is to use a Word document with a table as the data source, and that has a limit of 63 or 64 columns. To try it, you can copy/paste your Excel sheet into a Word document, using Word Edit|"Paste Special" and selecting Formatted Text (RTF) as the format. b. you would either have to automate Excel and Word to copy/paste (somehow or other) the data into Word, or maybe you could start with whatever source you are using to populate your Excel sheet. However, in that case, the question of how to get the data into Word while preserving the spaces does not go away easily. Peter Jamieson "ksg" wrote in message ... Thanks for the reply. Unfortunately I can not right justify and have the data look right. Here is an example of the data: 907__BASIC MET PROF 518__CBC W/DIFF 106__AFP 1600__COMP MET PROF 520__CBC W/O DIFF 428__CEA 915__LIPID PROF 546__PLATELET CT 210__CA 19-9 1601__HEP FUNC PROF 276__IRON PROF Each line is a formatted line of text that I am treating as a merge field. There is a lot of this data and there is no way to reliably parse the data into individual fields. When I merge the delimited file manually using Word it works fine with quotes, but using the program the merge document prints with the quotes. If you have any other ideas, please let me know. Thanks "Peter Jamieson" wrote: If you want to right-align all these texts you would probably be better off putting a right-aligned tab in Word immediately before the appropriate merge field. [Otherwise, unfortunately all the connection methods that Word can use to get data from Excel strip off both leading and trailing blanks, except the Excel converter, which is no longer provided with Word (since version 2002 or 2003, I forget which) or in the add-on converter pack. If you really want, I think you can get a copy of the converter from Graham Mayor's download page at http://www.gmayor.com/downloads.htm . Be aware that the converter has been withdrawn for a reason, has not been maintained for some time, and will always pop up a dialog whenever you connect using it] Peter Jamieson "ksg" wrote in message ... I have an automated mail merge program that accepts data from different systems. The data is in an Excel Sheet. Some of the data has blanks in the data. The data would look something like this: " DataField1" " DataFields2" etc. I need to keep the leading blanks so the data will line up on the merged document. I have tried enclosing the data in "" in the Excel Cell. This keeps the blanks, but the quotes print out on the document. Any help would be appreciated. Thanks |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mail merge keeps putting in decimals that are not on excel sheet | Mailmerge | |||
How do i mail merge in word from an excel sheet? | Mailmerge | |||
Problem with mail merge from Excel sheet to Word, HELP? | Mailmerge | |||
How do I enter a hyperlink from an excel sheet into mail merge? | Mailmerge | |||
How to specify Excel sheet name as mail merge source | Mailmerge |