Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a bookmark and applying a switch to it
Hi.
I have an external application which merges data from an Oracle DB. We need to perform a switch on the data to reflect currency however, the available /# switches do not apply and, instead, the field is then returned blank. Selecting F9 does not show the Mergefield codings. What I am reduced to understanding is that the mergefields are as is stored in the DB and cannot be represented. I saw a reference some time back to a process whereby the mergefield is brought into another section of the document and Bookmarked. Then, I call the bookmark and apply the currency formatting to it instead of the mergefield. Can anyone refer me to an article which could explain how to do this or perhaps, refer me to a sample VBA macro which can assist here. Thanks in advance. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a bookmark and applying a switch to it
Hi Mark,
Your document description suggests it's the post-merge file, rather than the main merge document. The merge process produces an output file in which the mailmerge fields are replaced with the relevant values. Any field modifications, therefore, need to be made in the main merge document. I note that your refer to "the available /# switches". The correct syntax for a numeric picture switch is a variant of '\# $,0.00'. Cheers -- macropod [MVP - Microsoft Word] "Mark" wrote in message ... Hi. I have an external application which merges data from an Oracle DB. We need to perform a switch on the data to reflect currency however, the available /# switches do not apply and, instead, the field is then returned blank. Selecting F9 does not show the Mergefield codings. What I am reduced to understanding is that the mergefields are as is stored in the DB and cannot be represented. I saw a reference some time back to a process whereby the mergefield is brought into another section of the document and Bookmarked. Then, I call the bookmark and apply the currency formatting to it instead of the mergefield. Can anyone refer me to an article which could explain how to do this or perhaps, refer me to a sample VBA macro which can assist here. Thanks in advance. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a bookmark and applying a switch to it
thanks for your response.
I agree with your assessment as a post-merge file and I have tried to edit the main-merge document but ot no avail. Something is happening in the middle which makes the switching difficult. I have set the switch as \# (just my Unix brain interfering for a moment!!) but still, these didn't work which lead me to the thought of using the values as bookmarks and formatting them or writing a VBA macro to do same. For what it is worth, the application uses generic numeric fields in the underlying DB as it is used for multi-currency and the vendors are of no assistance to how to get around this limitation. The fields are in a table with regular mergefield formatting and it is Office 2000. Thanks again. "macropod" wrote: Hi Mark, Your document description suggests it's the post-merge file, rather than the main merge document. The merge process produces an output file in which the mailmerge fields are replaced with the relevant values. Any field modifications, therefore, need to be made in the main merge document. I note that your refer to "the available /# switches". The correct syntax for a numeric picture switch is a variant of '\# $,0.00'. Cheers -- macropod [MVP - Microsoft Word] "Mark" wrote in message ... Hi. I have an external application which merges data from an Oracle DB. We need to perform a switch on the data to reflect currency however, the available /# switches do not apply and, instead, the field is then returned blank. Selecting F9 does not show the Mergefield codings. What I am reduced to understanding is that the mergefields are as is stored in the DB and cannot be represented. I saw a reference some time back to a process whereby the mergefield is brought into another section of the document and Bookmarked. Then, I call the bookmark and apply the currency formatting to it instead of the mergefield. Can anyone refer me to an article which could explain how to do this or perhaps, refer me to a sample VBA macro which can assist here. Thanks in advance. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a bookmark and applying a switch to it
The following kinds of manoevre have been known to help:
{ SET mylocalnumber { MERGEFIELD mynumber } } { REF mylocalnumber \#0.00 } { QUOTE { MERGEFIELD mynumber } \#0.00 } { SET mylocalnumber { QUOTE { MERGEFIELD mynumber } } } (All the "{}" pairs have to be the special field code braces you can insert using ctrl-F9) However, Word does sometimes have problems with certain types of numeric data - e.g. "currency" type fields in Access databases have not always behaved as you might hope. This could be another of those situations. If you insert a plain { MERGEFIELD mynumber } field, what do you see? Unfortunately, I don't currently have an Oracle DBMS to test things with, but assuming you are getting your data "directly" from your Oracle database (i.e., in Word 2000, via an Oracle ODBC driver), there are other things you could try, e.g. a. there may still be two Oracle ODBC drivers - one provided by Microsoft, which takes you up to about Oracle 6 in compatibility terms but AFAIK can read compatible type fields in later Oracle versions, and one provided by Oracle (there are other third party drivers as well). Perhaps worth trying both. b. Also worth exploring any options in the ODBC driver configuration. c. Using Word VBA and the OpenDataSource method you can issue SQL that may be able to transform these numeric fields into something that Word can handle. However, the length of the SQL statement is fairly limited (255 or 511 bytes or characters) and if you need to transform a lot of these numbers the SQL may become too long. This assumes that you cannot create your own views in Oracle to give you exactly what you need. Going a bit further, other potential solutions include: a. getting the Oracle data via another route (e.g. a Jet linked table, or via a csv format file) b. using Word VBA and the Mailmerge objects to preprocess the data for each record before it is merged c. using Word VBA to do one merge per record and preprocess the data for each record before it is merged. Let's hope one of the early suggestions (or one of macropod's) does the trick. otherwise, you will find macros that can do (c) if you search this group using Google Groups, or I can post something here. Peter Jamieson "Mark" wrote in message ... Hi. I have an external application which merges data from an Oracle DB. We need to perform a switch on the data to reflect currency however, the available /# switches do not apply and, instead, the field is then returned blank. Selecting F9 does not show the Mergefield codings. What I am reduced to understanding is that the mergefields are as is stored in the DB and cannot be represented. I saw a reference some time back to a process whereby the mergefield is brought into another section of the document and Bookmarked. Then, I call the bookmark and apply the currency formatting to it instead of the mergefield. Can anyone refer me to an article which could explain how to do this or perhaps, refer me to a sample VBA macro which can assist here. Thanks in advance. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a bookmark and applying a switch to it
Hi Peter,
Thanks for your very detailed reply. Unfortunately, I came up short in getting the transforms on the fields from working. The document is presented post-merge so it is as if the data fields are 'normal' text (as if the merged data was hard-coded into the template if that makes sense). I cannot view through ALT-F9 or otherwise the field codes. Custom views, different ODBC drivers or messing with the SQL is not possible as this is built within the executable which performs the merge function so I am residing to the option of formatting the text in the finished document. I am interested in the Macros that you referred to and woudl really appreciate any link you may have to one. Specifically, there are about 6 currency figures contained within a table underneath each other. Thanks again for your suggestions.. Mark A macro is probably the best solution Failing a satisfactory outcome from them - I'll "Peter Jamieson" wrote: The following kinds of manoevre have been known to help: { SET mylocalnumber { MERGEFIELD mynumber } } { REF mylocalnumber \#0.00 } { QUOTE { MERGEFIELD mynumber } \#0.00 } { SET mylocalnumber { QUOTE { MERGEFIELD mynumber } } } (All the "{}" pairs have to be the special field code braces you can insert using ctrl-F9) However, Word does sometimes have problems with certain types of numeric data - e.g. "currency" type fields in Access databases have not always behaved as you might hope. This could be another of those situations. If you insert a plain { MERGEFIELD mynumber } field, what do you see? Unfortunately, I don't currently have an Oracle DBMS to test things with, but assuming you are getting your data "directly" from your Oracle database (i.e., in Word 2000, via an Oracle ODBC driver), there are other things you could try, e.g. a. there may still be two Oracle ODBC drivers - one provided by Microsoft, which takes you up to about Oracle 6 in compatibility terms but AFAIK can read compatible type fields in later Oracle versions, and one provided by Oracle (there are other third party drivers as well). Perhaps worth trying both. b. Also worth exploring any options in the ODBC driver configuration. c. Using Word VBA and the OpenDataSource method you can issue SQL that may be able to transform these numeric fields into something that Word can handle. However, the length of the SQL statement is fairly limited (255 or 511 bytes or characters) and if you need to transform a lot of these numbers the SQL may become too long. This assumes that you cannot create your own views in Oracle to give you exactly what you need. Going a bit further, other potential solutions include: a. getting the Oracle data via another route (e.g. a Jet linked table, or via a csv format file) b. using Word VBA and the Mailmerge objects to preprocess the data for each record before it is merged c. using Word VBA to do one merge per record and preprocess the data for each record before it is merged. Let's hope one of the early suggestions (or one of macropod's) does the trick. otherwise, you will find macros that can do (c) if you search this group using Google Groups, or I can post something here. Peter Jamieson "Mark" wrote in message ... Hi. I have an external application which merges data from an Oracle DB. We need to perform a switch on the data to reflect currency however, the available /# switches do not apply and, instead, the field is then returned blank. Selecting F9 does not show the Mergefield codings. What I am reduced to understanding is that the mergefields are as is stored in the DB and cannot be represented. I saw a reference some time back to a process whereby the mergefield is brought into another section of the document and Bookmarked. Then, I call the bookmark and apply the currency formatting to it instead of the mergefield. Can anyone refer me to an article which could explain how to do this or perhaps, refer me to a sample VBA macro which can assist here. Thanks in advance. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a bookmark and applying a switch to it
It would be a simple task to create a macro that iterated through the cells
of the table that contain the numbers and format them the way you wish. Is it the only table in the document, if not, which one? What column in the tables are the value in? Does the table have a header row? The following code will apply currency formatting to the numbers in the third column a the first table in a document, starting with the number in the second row Dim i As Long Dim number As Range With ActiveDocument.Tables(1) For i = 2 To .Rows.Count Set number = .Cell(i, 3).Range number.End = number.End - 1 number.Text = Format(number.Text, "$#,###.00") Next i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Mark" wrote in message ... Hi Peter, Thanks for your very detailed reply. Unfortunately, I came up short in getting the transforms on the fields from working. The document is presented post-merge so it is as if the data fields are 'normal' text (as if the merged data was hard-coded into the template if that makes sense). I cannot view through ALT-F9 or otherwise the field codes. Custom views, different ODBC drivers or messing with the SQL is not possible as this is built within the executable which performs the merge function so I am residing to the option of formatting the text in the finished document. I am interested in the Macros that you referred to and woudl really appreciate any link you may have to one. Specifically, there are about 6 currency figures contained within a table underneath each other. Thanks again for your suggestions.. Mark A macro is probably the best solution Failing a satisfactory outcome from them - I'll "Peter Jamieson" wrote: The following kinds of manoevre have been known to help: { SET mylocalnumber { MERGEFIELD mynumber } } { REF mylocalnumber \#0.00 } { QUOTE { MERGEFIELD mynumber } \#0.00 } { SET mylocalnumber { QUOTE { MERGEFIELD mynumber } } } (All the "{}" pairs have to be the special field code braces you can insert using ctrl-F9) However, Word does sometimes have problems with certain types of numeric data - e.g. "currency" type fields in Access databases have not always behaved as you might hope. This could be another of those situations. If you insert a plain { MERGEFIELD mynumber } field, what do you see? Unfortunately, I don't currently have an Oracle DBMS to test things with, but assuming you are getting your data "directly" from your Oracle database (i.e., in Word 2000, via an Oracle ODBC driver), there are other things you could try, e.g. a. there may still be two Oracle ODBC drivers - one provided by Microsoft, which takes you up to about Oracle 6 in compatibility terms but AFAIK can read compatible type fields in later Oracle versions, and one provided by Oracle (there are other third party drivers as well). Perhaps worth trying both. b. Also worth exploring any options in the ODBC driver configuration. c. Using Word VBA and the OpenDataSource method you can issue SQL that may be able to transform these numeric fields into something that Word can handle. However, the length of the SQL statement is fairly limited (255 or 511 bytes or characters) and if you need to transform a lot of these numbers the SQL may become too long. This assumes that you cannot create your own views in Oracle to give you exactly what you need. Going a bit further, other potential solutions include: a. getting the Oracle data via another route (e.g. a Jet linked table, or via a csv format file) b. using Word VBA and the Mailmerge objects to preprocess the data for each record before it is merged c. using Word VBA to do one merge per record and preprocess the data for each record before it is merged. Let's hope one of the early suggestions (or one of macropod's) does the trick. otherwise, you will find macros that can do (c) if you search this group using Google Groups, or I can post something here. Peter Jamieson "Mark" wrote in message ... Hi. I have an external application which merges data from an Oracle DB. We need to perform a switch on the data to reflect currency however, the available /# switches do not apply and, instead, the field is then returned blank. Selecting F9 does not show the Mergefield codings. What I am reduced to understanding is that the mergefields are as is stored in the DB and cannot be represented. I saw a reference some time back to a process whereby the mergefield is brought into another section of the document and Bookmarked. Then, I call the bookmark and apply the currency formatting to it instead of the mergefield. Can anyone refer me to an article which could explain how to do this or perhaps, refer me to a sample VBA macro which can assist here. Thanks in advance. |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a bookmark and applying a switch to it
None of the other things I suggested reallly has a hope of being useful in
the environment you describe, so I'd start with Doug's suggestion and take it from there. Peter Jamieson "Mark" wrote in message ... Hi Peter, Thanks for your very detailed reply. Unfortunately, I came up short in getting the transforms on the fields from working. The document is presented post-merge so it is as if the data fields are 'normal' text (as if the merged data was hard-coded into the template if that makes sense). I cannot view through ALT-F9 or otherwise the field codes. Custom views, different ODBC drivers or messing with the SQL is not possible as this is built within the executable which performs the merge function so I am residing to the option of formatting the text in the finished document. I am interested in the Macros that you referred to and woudl really appreciate any link you may have to one. Specifically, there are about 6 currency figures contained within a table underneath each other. Thanks again for your suggestions.. Mark A macro is probably the best solution Failing a satisfactory outcome from them - I'll "Peter Jamieson" wrote: The following kinds of manoevre have been known to help: { SET mylocalnumber { MERGEFIELD mynumber } } { REF mylocalnumber \#0.00 } { QUOTE { MERGEFIELD mynumber } \#0.00 } { SET mylocalnumber { QUOTE { MERGEFIELD mynumber } } } (All the "{}" pairs have to be the special field code braces you can insert using ctrl-F9) However, Word does sometimes have problems with certain types of numeric data - e.g. "currency" type fields in Access databases have not always behaved as you might hope. This could be another of those situations. If you insert a plain { MERGEFIELD mynumber } field, what do you see? Unfortunately, I don't currently have an Oracle DBMS to test things with, but assuming you are getting your data "directly" from your Oracle database (i.e., in Word 2000, via an Oracle ODBC driver), there are other things you could try, e.g. a. there may still be two Oracle ODBC drivers - one provided by Microsoft, which takes you up to about Oracle 6 in compatibility terms but AFAIK can read compatible type fields in later Oracle versions, and one provided by Oracle (there are other third party drivers as well). Perhaps worth trying both. b. Also worth exploring any options in the ODBC driver configuration. c. Using Word VBA and the OpenDataSource method you can issue SQL that may be able to transform these numeric fields into something that Word can handle. However, the length of the SQL statement is fairly limited (255 or 511 bytes or characters) and if you need to transform a lot of these numbers the SQL may become too long. This assumes that you cannot create your own views in Oracle to give you exactly what you need. Going a bit further, other potential solutions include: a. getting the Oracle data via another route (e.g. a Jet linked table, or via a csv format file) b. using Word VBA and the Mailmerge objects to preprocess the data for each record before it is merged c. using Word VBA to do one merge per record and preprocess the data for each record before it is merged. Let's hope one of the early suggestions (or one of macropod's) does the trick. otherwise, you will find macros that can do (c) if you search this group using Google Groups, or I can post something here. Peter Jamieson "Mark" wrote in message ... Hi. I have an external application which merges data from an Oracle DB. We need to perform a switch on the data to reflect currency however, the available /# switches do not apply and, instead, the field is then returned blank. Selecting F9 does not show the Mergefield codings. What I am reduced to understanding is that the mergefields are as is stored in the DB and cannot be represented. I saw a reference some time back to a process whereby the mergefield is brought into another section of the document and Bookmarked. Then, I call the bookmark and apply the currency formatting to it instead of the mergefield. Can anyone refer me to an article which could explain how to do this or perhaps, refer me to a sample VBA macro which can assist here. Thanks in advance. |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a bookmark and applying a switch to it
Hi Doug, Peter and Macropod,
Doug, your suggested Macro works a treat! It is the immediate solution that I'll use although I would prefer the vendors to provide some more flexibility as it is only applicable to this one template and we use up to 20 - may make for an interesting Toolbar! Thanks for all your assistance. Mark "Doug Robbins - Word MVP" wrote: It would be a simple task to create a macro that iterated through the cells of the table that contain the numbers and format them the way you wish. Is it the only table in the document, if not, which one? What column in the tables are the value in? Does the table have a header row? The following code will apply currency formatting to the numbers in the third column a the first table in a document, starting with the number in the second row Dim i As Long Dim number As Range With ActiveDocument.Tables(1) For i = 2 To .Rows.Count Set number = .Cell(i, 3).Range number.End = number.End - 1 number.Text = Format(number.Text, "$#,###.00") Next i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Mark" wrote in message ... Hi Peter, Thanks for your very detailed reply. Unfortunately, I came up short in getting the transforms on the fields from working. The document is presented post-merge so it is as if the data fields are 'normal' text (as if the merged data was hard-coded into the template if that makes sense). I cannot view through ALT-F9 or otherwise the field codes. Custom views, different ODBC drivers or messing with the SQL is not possible as this is built within the executable which performs the merge function so I am residing to the option of formatting the text in the finished document. I am interested in the Macros that you referred to and woudl really appreciate any link you may have to one. Specifically, there are about 6 currency figures contained within a table underneath each other. Thanks again for your suggestions.. Mark A macro is probably the best solution Failing a satisfactory outcome from them - I'll "Peter Jamieson" wrote: The following kinds of manoevre have been known to help: { SET mylocalnumber { MERGEFIELD mynumber } } { REF mylocalnumber \#0.00 } { QUOTE { MERGEFIELD mynumber } \#0.00 } { SET mylocalnumber { QUOTE { MERGEFIELD mynumber } } } (All the "{}" pairs have to be the special field code braces you can insert using ctrl-F9) However, Word does sometimes have problems with certain types of numeric data - e.g. "currency" type fields in Access databases have not always behaved as you might hope. This could be another of those situations. If you insert a plain { MERGEFIELD mynumber } field, what do you see? Unfortunately, I don't currently have an Oracle DBMS to test things with, but assuming you are getting your data "directly" from your Oracle database (i.e., in Word 2000, via an Oracle ODBC driver), there are other things you could try, e.g. a. there may still be two Oracle ODBC drivers - one provided by Microsoft, which takes you up to about Oracle 6 in compatibility terms but AFAIK can read compatible type fields in later Oracle versions, and one provided by Oracle (there are other third party drivers as well). Perhaps worth trying both. b. Also worth exploring any options in the ODBC driver configuration. c. Using Word VBA and the OpenDataSource method you can issue SQL that may be able to transform these numeric fields into something that Word can handle. However, the length of the SQL statement is fairly limited (255 or 511 bytes or characters) and if you need to transform a lot of these numbers the SQL may become too long. This assumes that you cannot create your own views in Oracle to give you exactly what you need. Going a bit further, other potential solutions include: a. getting the Oracle data via another route (e.g. a Jet linked table, or via a csv format file) b. using Word VBA and the Mailmerge objects to preprocess the data for each record before it is merged c. using Word VBA to do one merge per record and preprocess the data for each record before it is merged. Let's hope one of the early suggestions (or one of macropod's) does the trick. otherwise, you will find macros that can do (c) if you search this group using Google Groups, or I can post something here. Peter Jamieson "Mark" wrote in message ... Hi. I have an external application which merges data from an Oracle DB. We need to perform a switch on the data to reflect currency however, the available /# switches do not apply and, instead, the field is then returned blank. Selecting F9 does not show the Mergefield codings. What I am reduced to understanding is that the mergefields are as is stored in the DB and cannot be represented. I saw a reference some time back to a process whereby the mergefield is brought into another section of the document and Bookmarked. Then, I call the bookmark and apply the currency formatting to it instead of the mergefield. Can anyone refer me to an article which could explain how to do this or perhaps, refer me to a sample VBA macro which can assist here. Thanks in advance. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up business stationery personalized by office user? | Microsoft Word Help | |||
Bookmark page cross-references not updating | Microsoft Word Help |