Mail Merge Time (suddenly) displaying as decimals again

Have you tried breaking the mailmerge connection & re-establishing it? Have you tried repairing your Office installation (via Programs & Features in the Windows Control Panel)?

Cross-posted at: http://www.msofficeforums.com/mail-m...xcel-word.html
For cross-posting etiquette, please read RULE 8

Hi Jenn,

I don't think there's any issue with cross-posting, per se. The issue is that you should provide links for them so that people on one site don't waste time retracing ground that's already been picked over elsewhere. With links, they can see what's been suggested/tried elsewhere.

I'm still hoping to a resolution to this issue. I've observed that the final merged document is in "compatibility mode". Could this be a contributing factor to the decimal problem in my time fields?
Here is some history. We have recently upgraded to Office 2010 from 2003. Of course, with that change came a transformation from .doc to .docx formats. I went through each individual main mail merge document and made the necessary changes to reflect the new source file extension, modified some of the report, and saved it in .docx format.

However, the resulting merged document, although having a .docx extension indicates it's in compatibility mode.

Thoughts?

And as a second annoyance ... the merged document is minimized in the taskbar. When maximized, the toolbar and ribbon functions appear disabled for the document, ie. I can press any button and their is no affect on the document. I have to minimize it, and re-maximize it before I have functionality.

Compatablitiy mode doesn't have any effect - it mainly only means your 2010 docx document will be fully supported in Word 2007. Word 2007 doesn't support Checkbox Content Controls, for example.

Perhaps you should disconnect the mailmerge main documents from the data source and re-connect them using the DDE connection method.

Hi Paul.
I followed the Microsoft Knowledgebase document at http://support.microsoft.com/kb/320473 create the DDE link between my Excel datasource and my word main mail merge document.

When I run the report manually, it appears that the time proble has been resolved. The report merges flawlessly.

However, with this change, I have lost the functionality of the automation code I have in my Excel application which allows a user to click a button to initiate the merge. What is supposed to happen is, from a userform in Excel, the user clicks a button to initiate the merging of data into the document. The Excel macro opens word, selects the report, and launches to document to merge. Before this DDE thing, it worked, the only problem being the missed time formats.

What is happening now is ... the user clicks the button, and the application stalls. It takes a CTRL-ALT-DEL to recover. With this action, one can see that Word has executed in the background, but nothing appears to be happening. The user would end (Word) task, which results in Excel's "File in use" prompt. 'MergeData.xls' is in use and loced for editting."

Pressing "Read only" releases whatever action is screwing the merging up and the Word document appears successfully merged. The user is (inappropriately) prompted to save the changes to "MergeData.xlsx" ... the datasource ... which shouldn't be.

I think I'm back to this issue again ...

http://www.excelforum.com/excel-prog...html?p=2829216

Hi Jenn,

You could, of course, include the 'Read Only' parameter in to code that opens the Word document and, presumably, a command to close it and the datasource after the merge without saving changes.

Hi Jenn,

I can't confirm that my suggestion will 'fix' the problem. Indeed, I suspect the source of the problem may be due to the data structure in the '41064(04-June-12).xls' workbook in the other thread, which I assume is a mailmerge data source. In that workbook, you have a column named 'GAdv. Date', which has dates in some rows only and other columns, such as 'GTime' that have times in some rows only. When Word queries a data source for mailmerge purposes using the defaule OLE DB, it look at the first ~ 16 rows to determine the data type. If the data are not of a consistent type in all of those rows, the data type might be evaluated wrongly. This can result in text in alpha-numeric columns coming out as 0s, dates and numbers, times as decimals, and so on. Using the DDE format skips the evaluation step and simply returns the data strings according to their formats. For a discussion on all of this see: http://tips.pjmsn.me.uk/t0003.htm.

Re the file opening, I was thinking of something along the lines of:
Set odoc = objword.Documents.Open(FileName:=FName, ConfirmConversions:=False, ReadOnly:=True, AddToRecentFiles:=False)
Note also the 'ConfirmConversions' entry here. I suspect that its omission may have had something to do with the hanging when using DDE.

A wonderful explanation Paul in the difference in OLE DB and DDE, thank you for the time.

I was excited to apply and try your suggested code. Sadly, it continued to hang, interrupted within a few minutes with a message "MergeData.xlsx" (the source document) being locked for editing.
Selecting {READ ONLY} resulted in Word message "Word could not re-establish a DDE connection to Microsoft Excel to complete the current task.", then followed by a request to find the data source despite the path noted for it in the message being accurate. When you close the message (declining the options) word advises with a message that the file cannot be opened because there are problems with the contents. (Unspecified error Location: Part: /word/settings.xml, Line: 2, Column: 2934). Upon pressing OK, Word displays "Word found unreadble content in DR-RPL7.docx". Pressing No to recover the contents leads to a VBA error kmessage "Word could not re-establish a DDE connection to complete the current task", pointing to the line we just edited.

There are at this point two instances of the data source open in Excel, a normal one and a read only one.

This is painful.

By the way ... if I manually open the main mail merge document, everything works fine.

Jenn

Cross posted: http://www.mrexcel.com/forum/showthr...10#post3216910

OK, given that you macro seems to work when you're not using DDE, revert to that and use the following field code for the times:
{QUOTE{SET Val {MERGEFIELD GTime}}
{IF{Val}= "" "" {IF{Val \@ "HH:mm:ss"}= "??:*" {Val} {QUOTE
{SET ss{=INT(Val*86400+.5)}}
{SET hr{=INT(ss/3600)}}
{SET mn{=INT((ss-hr*3600)/60)}}
{SET sec{=MOD(ss,60)}}
{=IF(hr=0,12,hr) \# 00}:{mn \# 00}:{sec \# 00}}}}}

The above field code first checks whether the GTime data field has content and outputs nothing if it's empty. If not empty, the field code checks whether a time is returned and, if so, outputs that. If a decimal value is returned, the output is converted to a time.

If you don't want to input the field code manually (with proper field braces where the above has ordinary braces), you can download a macro I developed to convert the above, from: http://www.gmayor.com/export_field.htm#TextToField

Hi Jenn,

With what I posted for the field code, it would be a simple matter to create it once, then copy & paste to each of the other locations, changing just the mailmerge data source field name for each instance. Be aware that formatting the time values as simple text in Excel may not resolve the issues - it may, in fact, introduce new issues concerning how Word might evaluate the data, per post #11 above.

You should never have such mixed data in the field. In all likelihood, that's what's behind the problems you're having. If you have a data field that the mailmerge process evaluates as numeric, any text is liable to come out as 0s. Conversely, if it's evaluated as text, dates may come out as numbers (eg 1-1-2000 = 36526 (serial date #) or -2000) or decimals (via the date being treated as two divisions (eg 1/1/2000 = 0.005)) and times as decimals. If it's evaluated as variant, everything might come out OK. DDE works around that, but seems to be giving you other issues.

I could enhance the field code to handle text as well, but that'll be of no value if the mailmerge process evaluates the data as numeric.

Superficially, yes, it's odd, but minor settings/version differences (which I don't profess to be fully conversant with) could explain it.