Excel to Word Mail Merge, most fields reported as 0

Hello All,

I have been bothered by a mail merge issue that I have been unable to resolve. Quick background... Excel worksheet data is linked to cells on a 2nd sheet that is used as the data source ("Pull Sheet$"). The Pull Sheet has Merge fields set up on a Word Template.

Patient Name: «LAST_NAME», «FIRST_NAME»
MR#: «MRN»
Date of Service: «DATE_of_SERVICE»
DOB: «DOB»
Age: «AGE»
Gender: «GENDER»
Diagnosis: «ICD10_CODES»
Procedure: «PROCEDURE_»
Surgeon: «ATTENDING_SURGEON»
Hospital: «HOSPITAL»
IONM Technologist: «TECHNOLOGIST»
Start Time: «Case_Start»
End Time: «Case_End»
Total Billing Time: «TOTAL_BILLABLE_TIME» hour(s)


IONM modality 1: «Mod1»
IONM modality 2: «Mod2»
IONM modality 3: «Mod3»
IONM modality 4: «Mod4»
IONM modality 5: «Mod5»

The data is visible on the excel sheet, but through the macro, it somehow gets lost and not making it to the Word document. When the Word document is processed, it comes through as such:

Patient Name: 0, 0
MR#: 0
Date of Service: 01/00/1900
DOB: 01/00/1900
Age: 118 Years, 1 Months, 27 Days
Gender: 0
Diagnosis: ,
Procedure: 0
Surgeon: 0
Hospital: 0
IONM Technologist: 0
Start Time: 12:00:00 AM
End Time: 12:00:00 AM
Total Billing Time: 0 hour(s)


IONM modality 1: 0
IONM modality 2: 0
IONM modality 3: 0
IONM modality 4: 0
IONM modality 5: 0


I have tried to change the order of the data in the Pull Sheet as some have pointed out it may be getting lost on data type. I have also removed and redirected the data source using the full network path. Seems like I am covering the same ground.

Here is the merge macro that I found online and have adapted. Maybe someone will find where I got this off track.

Please Login or Register  to view this content.

The excel file is shared and read only if that makes any difference.

Your help is appreciated.

Patrick

Greetings Paul,

Thanks for the code update. Sadly,I am getting the same result. I am not sure why this merge will not work. I have changed the format of the Word document from .dotm to .docx without any change to the situation.

To recap a bit, I have a worksheet form, not userform, that links data to the 2nd row of "Pull Sheet" . Pull Sheet populates a running log via macro. This part works. The macro then moves on to a file save routine that creates a properly named folder and saves a new copy of the file with the proper name. This also With the original excel file still open, the merge macro is called. "Pull Sheet" is still populated but does not push the data into the merge. I can't understand what is holding it up. This project I am on, is a adaptation of something we have been using for years. Data set is basically the same. Layout is a little different, but that shouldn't matter since the data is getting posted to the "Pull Sheet" correctly.

Here is a screenshot of the "Pull Sheet" data:
Pull Sheet.JPG

Sorry the image is so small. As you can see though, the data is present. Not all the fields get populated during the initial fill of the document, some get put in after the fact. Those are the blank and missing data on the line.

The merge fields will always have the data to be populated.

If it matters, we are 64bit Office 2010 products here.

I am really stuck on this. I was hoping to get it sorted out to start the new month.



Patrick

Hi Paul,

Quick update. After going back and reworking the filesave macro which ran just prior to the mail merge macro, I was able to get it all working. Thank you for pointing me in the right direction.

Cheers,
Patrick