Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-16-2012, 05:46 PM
MellowMel MellowMel is offline Loss of formulae values when mail merging excel to word Windows XP Loss of formulae values when mail merging excel to word Office 2007
Novice
Loss of formulae values when mail merging excel to word
 
Join Date: Feb 2012
Posts: 1
MellowMel is on a distinguished road
Default Loss of formulae values when mail merging excel to word


I am doing a mail merge where I have an excel spreadsheet to merge into Word 2007. As it kept randomly reformatting fields, I have changed it to MX Excel Worksheets via DDE. Now when I merge, the fields which have formulae in them are not appearing as a merge field option. I can't find an answer to this anywhere on the web.
Reply With Quote
  #2  
Old 02-16-2012, 10:12 PM
macropod's Avatar
macropod macropod is online now Loss of formulae values when mail merging excel to word Windows 7 64bit Loss of formulae values when mail merging excel to word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi MellowMel,

I can't see why changing to DDE would make some fields unavailable, though columns that have mixed text & numeric data might not return the text data.

Without resorting to DDE, you can control the formatting in Word via field switches. Some of the examples below go beyond the use of switches, though, and entail the use of nested fields. So it's important to note that any field brace pairs (ie '{ }') for the nested field examples are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. Likewise, you can't type or copy & paste the chevrons (ie '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar.

Mailmerge String Formatting
to control mailmerge string formatting, add a 'Charformat' picture switch to the mergefield as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyData}, where 'MyData' is your data field's name;
• delete everything between 'MyData' and the closing field brace;
• add ' \* Charformat' after 'MyData', so that you end up with {MERGEFIELD MyData \* Charformat};
• format at least the 'M' in 'MERGEFIELD' with the font attributes you want;
• position the cursor anywhere in the field and press F9 to update it.
• run your mailmerge.

Mailmerge Number & Currency Formatting
To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:
• select the field;
• press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
• edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Note1: The '\# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:
. \# 0 for rounded whole numbers
. \# ,0 for rounded whole numbers with a thousands separator
. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
. \# $,0 for rounded whole dollars with a thousands separator
. \# "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values
The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

If you use a final ';' in the formatting switch with nothing following, (eg \# "$,0.00;($,0.00);") zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.

Note2: If you use a decimal tab or right-aligned tab to align the values and insert a tab into the field code after the $ sign, you can have the values output like:
$ 999,999.99
$ 9,999.99

Mailmerge Percentage Formatting
To control percentage formatting in Word, add a formula and numeric picture switch to the mergefield, as follows:
• select your mergefield, which will look something like «Percent»;
• press Ctrl-F9 to wrap another field around it, thus { «Percent» };
• edit the field so that you get {=«Percent»*100 \# 0.00%};
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Note: the precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

Mailmerge Phone Number Formatting
To control Phone Number formatting in Word, all you need to do is to add a picture switch to the mergefield, as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyData}, where 'MyData' is your data field's name;
• delete everything between 'MyData' and the closing field brace;
• add ' \# "(000) 0000 0000"' after 'MyData', so that you end up with {MERGEFIELD MyData \# "(000) 0000 0000"};
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Mailmerge Date Formatting
A limitation in the OLE DB provider used to get data from Excel etc., when Word is connected to an OLE DB data source, is that it treats dates as if they are in the US mm/dd/yy format, regardless of the format in Excel, your regional settings etc. Applying a date format switch fixes that - and gives the mailmerge document the ability to format the date independently of whatever format is used in the data source.

To get the date format you want, you can add a formatting picture switch as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
• delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy";
Note: Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months - lowercase 'm's are for minutes.
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

One thing you'll notice if you're using ordinary date fields in the mailmerge main document is that the date in the output document will update if you re-open the document at a later date, which can be problematic. There is a simple way to prevent this - embed the Date field in the mailmerge main document in a QUOTE field. To do this, simply select the date field, press Ctrl-F9 to embed it in another field, and type 'QUOTE' inside the left field brace, so that you end up with {QUOTE 'date field'}. With the QUOTE field, the date field will automatically unlink when the mailmerge output document is created.

Mailmerge Time Formatting
To get the time format you want, you can add a formatting picture switch as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyTime} where 'MyTime' is your mergefield's name;
• delete anything appearing after the mergefield's name and add '\@ "h:m"', as in {MERGEFIELD MyTime \@ "h:m am/pm"}.
Note: you must use lowercase 'm's for minutes - uppercase 'M's are for months ;
• if you want the hours and/or minutes to display leading 0s, change 'h' to 'hh for hours and 'm' to 'mm' for minutes;
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.

Mailmerge US Social Security Number Formatting
The following field suppresses all except the last four digits in a mergefield that gets its data from a field named ‘SSN’, where the data are formatted as ‘123-45-6789’.
{QUOTE
{SET ID {MERGEFIELD SSN}}
{SET Part3{=({ID}*(-1)-ID)/2}}"XXX-XX-"{Part3 \# 0000}}

Mailmerge US Zip Code Formatting
The following field code correctly formats 5-digit and 5+4-digit Zip Code data, even where the source data may have incorrect formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes (eg because a db only accepts Zip Codes in the 5+4-digit format) or is formatted as a 5-digit or 9-digit string without hyphens. A data field named ‘ZipCode’ is assumed.
{QUOTE
{SET Zip {MERGEFIELD ZipCode}}
{IF{Zip}> 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 05-07-2014, 06:53 AM
chaislip chaislip is offline Loss of formulae values when mail merging excel to word Windows 7 32bit Loss of formulae values when mail merging excel to word Office 2010 32bit
Novice
 
Join Date: May 2014
Posts: 3
chaislip is on a distinguished road
Default

Macropod, I have a mailmerge in the footer used for barcoding documents and I have encountered an issue similar to this that I am hoping you can help with. my barcodes have to contain a social security number for my doc imaging system to correctly index/archive the documents and it only recognizes numerical characters when reading the barcodes for the SSN field. The software that feeds the mailmerge in my documents, however, will only provide the SSN data in ###-##-#### format. Is there a way that I can have Word remove the hyphenation without just performing "subtraction" of the three different number sets?

Thanks.
Reply With Quote
  #4  
Old 05-07-2014, 04:08 PM
macropod's Avatar
macropod macropod is online now Loss of formulae values when mail merging excel to word Windows 7 32bit Loss of formulae values when mail merging excel to word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You can eliminate the spaces with a field coded as:
{QUOTE{SET ID {MERGEFIELD SSN}}{SET EXP1{=-{=-{ID}-ID}/2}}{SET EXP2{=-({ID}*(-1)-{ID})/2}}{SET EXP3{=({ID}*(-1)-ID)/2}}{=EXP1*1000000+EXP2*10000+EXP3 \#000000000}}
However, whether you'll be able to use that depends on how the other software to which you refer updates the document.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-08-2014, 02:13 PM
chaislip chaislip is offline Loss of formulae values when mail merging excel to word Windows 7 32bit Loss of formulae values when mail merging excel to word Office 2010 32bit
Novice
 
Join Date: May 2014
Posts: 3
chaislip is on a distinguished road
Default

Is there a special way I need to enter this information into Word for this to work? If it would be helpful for you, I have uploaded the sample mailmerge database file I have been working with so that the field name can be correct.
Attached Files
File Type: zip Mailmerge temp.zip (4.6 KB, 8 views)
Reply With Quote
  #6  
Old 05-08-2014, 02:43 PM
macropod's Avatar
macropod macropod is online now Loss of formulae values when mail merging excel to word Windows 7 32bit Loss of formulae values when mail merging excel to word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

As indicated in post #2, the example assumes "mergefield that gets its data from a field named ‘SSN’, where the data are formatted as ‘123-45-6789’". Since your database field is named ‘B1_SS_NUM’, you should use that instead.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 05-09-2014, 09:08 AM
chaislip chaislip is offline Loss of formulae values when mail merging excel to word Windows 7 32bit Loss of formulae values when mail merging excel to word Office 2010 32bit
Novice
 
Join Date: May 2014
Posts: 3
chaislip is on a distinguished road
Default

Macropod,

That worked perfectly. Thanks so much for providing me with the necessary field data to generate the results I needed.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Loss of formulae values when mail merging excel to word Mailing: Reading values from Excel to Word Jamal NUMAN Excel 9 09-02-2011 02:08 AM
Merging word files from excel macro hklein Excel Programming 0 08-05-2011 02:27 AM
Mail merging and pulling varying data from Excel shannag1881 Mail Merge 0 10-05-2009 08:51 AM
Word - Calculate and paste values from Excel sheet Augf87 Word 1 07-06-2009 10:26 AM
HELP! Merging Excel to Word peevenjo Mail Merge 0 07-06-2007 03:24 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:56 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft