I am trying to run mailmerge in one template word2016 (docm) file with another excel (xlsm) worksheet being the data source.
While most of the fields work just fine and prefect, i have a column called "PCI" containing either blank cell or alpha-numeric data. For the mail merge field refering to this column, it keeps wrongly outputing 12:00:00AM whenever the column is not blank. But what i want is the field showing either nothing or the exact same alpha-numeric information direcrly.

The followings are something i believe to have something doing with this error:

1. Unlike any other columns, the first 8 records of this column are all blank. I know that OLE DB connection somehow use the first 8 records to figure out the cell format. So i wonder this may be the reason why it fails to interpret the column as text format.

2. To make it ever more complicated, my ideal practice is to run a vba from the excel data source to open the word template->open the mailmerge function->user inputs the desired id of particular record in the source->save individual work file of that record. While most of these work just fine, i realize that this error only happens when i open the work file with my excel source file also opened. If i do it only in the word file without opening the excel, the proper alpha-numeric data just shows up prefectly. The error only occurs when i open the word file by the vba from excel.

Can anyone give some advice on how to tackle the problem? I know i can simplify the problem by not opening the excel, but i really want to know where does it go wrong. Thank you.