Inconsistent behavior of MS Word mail merge depending on open/closed state of data source MS Excel

Question

I am doing a mail merge in MS Word based on an Excel source.

I used the Wizard to set up the source and it successfully imports the Excel data to MS Word.

However, the formatting of the date is absolutely wrong and depends on whether Excel is open when the Word document is opened.

Description of the source:

where the dates are formatted according to the French dating system (Format Cells>Number>Dates>*dd-MM-yyyy). My Operating System has a French localisation, my MS Office is installed in French, so there is (read: should be) no conflit of regional localisation. Problem remains when selecting "dd-MM-yyyy".

Output in MS Word when Excel source is open:

• With Alt+F9 enabled

• When displaying the results

Output in MS Word when Excel source is closed:

• When displaying the results:

So, my question is: is it a normal behavior? Is there a work-around?

The issue is that I need to have the Excel source open when I open the Word document because it is part of a workflow (I created an hyperlink in Excel that, when clicked, opens the Word document - see below)

It gest worse.

When I use the same date-time switch on both Mergefields "Entrée" and "Sortie", one is in the European format and the other is in the US date format (Excel source closed):

Hypothesis 1: the date-time switch actually does not do anything and actually uses the US date format (since 25 cannot be a month, it must be a European format). 

Testing hypothesis 1: having the second date-time switch as MM-dd-yyyy:

It works (both display European format), but it does not make sense because, for the same date format in the Excel source between “Entrée” and “Sortie”, I have two different date-time switches. 

It gets even worse.

Let us now go to the second record which has the same date format as the first record (I applied the style to the whole column in Excel). Now, the date-time switch displays it in the American format (MM-dd-yyyy). So let us switch back to the European format for the second switch:

… and now I have the same switch, but two different outcomes… one in the US format (with a switch in US date format) and one in the European date format (with a switch in the European date format)!

So, to sum up:

With same source date format, same date-time switch: different date display between “Entrée” and “Sortie”

With same source date format, different date-time switch: same date display

Keeping the date-time switches as they are, changing the record changes the display of “Entrée” and “Sortie”

What am I missing??

Thank you for your help.

EDIT: I delete the security check for the SQL query in the registry (https://support.microsoft.com/en-au/help/825765/you-receive-the-opening-this-will-run-the-following-sql-command-messag) and the minimum working example (see below) now pulls the dates correctly. However, my previous problem remains. In conclusion, it seems that it is file-specific. How can I ensure that all the options are the same?

You can find the Minimum Working Example here. The password is "mail_merge".

Answer 1

The date format in word is being amended

https://support.office.com/en-us/article/Prepare-your-Excel-data-source-for-a-Word-mail-merge-2d802b6b-a3a3-43e5-bb76-2cac7c68673e

Answer 2

I am far from an expert at this although I use Mail Merge almost daily.

Paul Edstein, who answered in your other thread, knows far more than do I.

Mailmerge Tips & Tricks

I'm afraid Palcouk's reference page will not help you.

The number you are getting when Excel is open is the actual date in Excel's storage method, unformatted.

I have never noticed this happening. I just checked in a file I routinely use and observe no anomaly.

If you want, save a sample primary merge file and data source, both without confidential information, where you observe this, on DropBox and save links here so we can see if we can reproduce this.

Answer 3

So, my question is: is it a normal behavior? Is there a work-around?

The issue is that I need to have the Excel source open when I open the Word document because it is part of a workflow (I created an hyperlink in Excel that, when clicked, opens the Word document - see below)

The behaviour is not uncommon and typically results from having the Excel workbook open when you open the mailmerge main document; it usually doesn't occur if you open the mailmerge main document  before opening the Excel workbook.

If you normally have the workbook open when you open the mailmerge main document, see the workaround described in Importing Date and Time Values From Excel and Access in my Microsoft Word Date Calculation Tutorial, available at:

http://www.msofficeforums.com/word/38719-microsoft-word-date-calculation-tutorial.html

or:

http://www.gmayor.com/downloads.htm#Third_party

Do read the document's introductory material.

Answer 4

How are you connecting to the data source.  I would suggest using the OLE DB Database Files method of connection, and not the DDE method.

Answer 5

Dear Charles,

Thanks again for your help.

You will find the files for the minimum working example here. The password is "mail_merge"

The important sheets in Excel are:

• "Informations_à_compléter" (first sheet) to try with a new data entry. Please use the calendar widget to insert the dates as this will correspond the most closely to the actual process.
• "Contrats" (third sheet) to trigger the opening of the corresponding Word document with the link in the F column

In Word, the important information for mail merge is found at around the middle of the first box (Fields "Entrée" and "Sortie").

Best,

Olivier.

Disclaimer: my parents have a little cottage to rent and I try to automate as best I can the process. Although commercial in nature, this project has a very limited scope and has little commercial value, if any.