Ina mail merge document, one of the fields is a dollar amount. The data source (Excel file) has some values with two zeros after the decimal and others have two other digits. The merge brings in the double zero values as having no decimal places and the values with two other digits come in correctly.
Any idea why this is? Or how to correct it? The Excel column is formatted as currency with two decimal place.
Mail merge not pulling in entire number
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
-
- Administrator
- Posts: 78443
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Mail merge not pulling in entire number
There are basically two solutions:
One option is to add a format switch to the date mailmerge field in Word:
- Right-click the merge field.
- Select Toggle Field Codes from the popup menu.
- Insert the following before the closing brace } but after the merge field name.
\# "0.00"
- Press F9 to hide the field code and to update the field.
Another method:
- In Word, select Tools | Options... (in Word 2007 or later, click the Office button, then Word Options...).
- Activate the General tab (in Word 2007 or later, click Advanced, and scroll down to General).
- Tick the "Confirm conversion at Open" check box.
- Click OK.
- Go back to step 3 of the mail merge wizard (task pane).
- Click "Select a different list".
- Locate your workbook again.
- This time, you'll be prompted for a method to connect to the workbook.
- Select "MS Excel worksheets via DDE (*.xls)", then click OK.
- Continue as usual.
The mail merge should now preserve the formatting from Excel.
One option is to add a format switch to the date mailmerge field in Word:
- Right-click the merge field.
- Select Toggle Field Codes from the popup menu.
- Insert the following before the closing brace } but after the merge field name.
\# "0.00"
- Press F9 to hide the field code and to update the field.
Another method:
- In Word, select Tools | Options... (in Word 2007 or later, click the Office button, then Word Options...).
- Activate the General tab (in Word 2007 or later, click Advanced, and scroll down to General).
- Tick the "Confirm conversion at Open" check box.
- Click OK.
- Go back to step 3 of the mail merge wizard (task pane).
- Click "Select a different list".
- Locate your workbook again.
- This time, you'll be prompted for a method to connect to the workbook.
- Select "MS Excel worksheets via DDE (*.xls)", then click OK.
- Continue as usual.
The mail merge should now preserve the formatting from Excel.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Mail merge not pulling in entire number
Thanks.. how do I get the task pane to reveal itself? Word 2007.
Don
-
- Administrator
- Posts: 78443
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Mail merge not pulling in entire number
Activate the Mailings tab of the Ribbon.
Click Start Mail Merge > Step by Step Mail Merge Wizard.
Click Start Mail Merge > Step by Step Mail Merge Wizard.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Mail merge not pulling in entire number
Okay, I got that far. Now when I open the Excel file (which must only be .XLS), I am unable to choose the tab with the merge data. Must it be in a separate file?
Don
-
- Administrator
- Posts: 78443
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Mail merge not pulling in entire number
I think the DDE method expects the data to be in the first tab. If you use the default OLE DB method (or ODBC), you can select the sheet, but then you'll have to add the formatting switch to the merge field(s).
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Mail merge not pulling in entire number
Thanks Hans, I found the switches to be more efficient. The other method was continually problematic.
Don
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: Mail merge not pulling in entire number
DDE is an older technology (circa 1992) and doesn't recognize some of the more recent innovations such as multiple tabs in Excel workbooks. I keep expecting Microsoft to pull the plug on it's support, but so far it continues to work. And surprisingly, it often works better than ODBC or OLE DB. I guess they have taken the adage "if it ain't broke, don't fix it" to heart.
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!