Mail Merge

[StarhawkGamer]

I'm attempting to do a mail merge with a Word 2007 document and an Excel 2007 Macro-Enabled Worksheet (*.xlsm) (using VBA to create statements based on information being reported). A couple of fields can either be a null/blank value or a numeric value. The problem I'm having is that the null values are coming up as zeroes. In excel, I'm pulling data from 5 different spreadsheets into a single, output worksheet, using the following equation:

=IF(VLOOKUP(C166,Scorecard!B:H,7,FALSE)="","",VLOOKUP(C166,Scorecard!B:H,7,FALSE))

This same equation on some fields works, but on others doesn't.

 

[SkipVought]

"In excel, I'm pulling data from 5 different spreadsheets into a single, output worksheet"

I would NOT recommend using a lookup formula to gather data like this. MS Query would be MUCH easier and cleaner. I believe in Excel 2007, it is Other Data Sources in the External Data menu, (going from memory, like the 3rd icon in)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Hi StarhawkGamer,

The problem I'm having is that the null values are coming up as zeroes.

If the problem is with the merged output, then a simple formatting switch on the mergefield should fix it.

For example,if select the problem mergefield and press Shift-F9, you should see something like:

{MERGEFIELD Data} or {MERGEFIELD Data \* MERGEFORMAT}

To generate only non-zero outputs, change this to:

{MERGEFIELD Data \# ",0;-,0;"} or {MERGEFIELD Data \# ",0.00;-,0.00;"}

where the number of 0s after the decimal point determines the precision and the comma is the thousands separator. You can also add a '$', '£', '¥' or '€' symbol if you want the result displayed as currency - eg:

{MERGEFIELD Data \# "$,0.00;-$,0.00;"}

Press F9 when you're done to update the field. Your mailmerge is now ready to run.

Cheers

[MS MVP - Word]

 

[StarhawkGamer]

Both of these suggestions have been useful. In the end, the problem was easily cured just by copying the worksheet off of the macro-version (doing a paste value), sorting the affected columns, and deleting the contents of the blank cells, and then targeting the Mail Merge to the non-macro document.

 

[rayna]

This is an incredibly basic question - but I just got a new machine and have been "upgraded" from Word 2003 to Word 2007.

In my job, I use current Word Documents, and add mergefields to them to pull data from our database and print completed documents. The original docs are/were normal Word documents, and that used to work just fine. In 2003, I simply hit the drop down menu and added fields. Easy! In 2007, under Mailings, the "Insert Merge Field" option is greyed out...and I have no idea why, or worse how to stop it from being so so that I can access it to drop in the appropriate database fields.

Does anyone know what I am doing wrong? Any help would be appreciated, because as it stands, I am having to borrow colleague's computers to use their Word 2003 to get my job done. I am going to have to go back to 2003 if I can't figure this out.

Man plans. God laughs.

 

[rayna]

I am sorry...I tried to post in the wrong section and it won't let me delete it!

Rayna

Man plans. God laughs.

 

[macropod]

Hi rayna,

If you can see at least some of the mergefields in the document, perhaps the document is no longer connected to its data source and you'll need to re-connect the data source.

If you can't see any mergefields in the document, it's probably been converted from a mailmerge main document to a plain document. In that case, you'll have to re-establish it as a mailmerge main document.

[MS MVP - Word]