#  Other Applications & Softwares  > Word Formatting & General >  > [SOLVED] Mail Merge Time (suddenly) displaying as decimals again

## Jenn68

Augh! Panic.

My main mail merge documents are no longer accepting my Excel time formats. Until today, my time mergefields, formatted with the proper switch {MERGEFIELD "Start" \@ "h:mm am/pm}, are displaying my Excel database's time formatted values as decimals!!

I am using Word 2010 with "Confirm file format conversion on open" checked. What is odd, is the dates are displaying properly using Excel date formatted values.

Please help ... :-(

Jenn

----------


## Jenn68

Hmmmm ... whats even more odd, is this problem doesn't exist on my home computer.

----------


## macropod

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*

----------


## Jenn68

Thank you Paul for the lesson on cross posting etiquette. I felt that by posting to one or two different forums I would reach a broader audience of knowledgeable individuals. I figured that people are loyal to one or two sites, and that I might capture one or two at one site that can offer help, that aren't at another site.

I think my code may be to blame for this, and likely other strange, issues. I have discovered that this problem is not replicated when I manually initiate a mail merge. That is, if I open the main merge document and press the merge button, the result is flawless. What I did find additionally, that the merge will fail (ie the decimal time formatting problem) if the data source is visibly open ... whether minimized or maximized. If it's in the taskbar, or open behind another window, the merge will fail. It will fail whether manually initiated, or by the automating code. 

Whether it's this code, or code leading up to the merge routine, it always seems like the data source is open ... and all kinds of weird things happen.

I just find it a curiosity that it only happens in Office 2010. I do not have these problems on my home computer running Office 2007 with the same code and files. The 2010 installation is brand new, on an educational network of brand new computers. I hope the Office Suite install hasn't been corrupted yet after a week of use.

Jenn

----------


## macropod

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.

----------


## Jenn68

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.

----------


## macropod

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.

----------


## Jenn68

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

----------


## macropod

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.

----------


## Jenn68

Paul, thanks for your continued support, but I'm claiming ignorance.

In your opinion, will your suggestion resolve the hanging issue as well? It is a far more inconvenient problem (having to CTRL-ALT-DEL and end (Word) task) than the saving prompt. I'm not quite clear on how, or what file, would have the "read only" attribute set to? The main mail merge document (DR-RPL.docx) or the datasource (MergeData.xlsx)?

And similarly, not sure how to code closing both documents once the merge is complete. Pre DDE I never encountered this problem. Does initiating a merge with DDE change the main mail merge document everytime it's opened?

Jen

----------


## macropod

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.

----------


## Jenn68

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

----------


## macropod

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

----------


## Jenn68

Wow ... thats quite the field! I appreciate your help immensly Paul. If I haven't resolved the issue, I certianly learned a few things.

Although I rely on the data source for other functions within my application requiring time calculations, I think at this point I am going to consider just simply converting all my data from Excel formatted (time/date) values to simple text, and worry about the calculations inside the code as they are needed. It will require some coding changes, but I have far too many time fields in my reports to work with your advanced field. Certianly, if the text conversion fails, I will certianly revisit your solution.

I'll report back with success or failure.

----------


## macropod

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.

----------


## Jenn68

Hmmmm. Fair enough consideration Paul. I'll see how well I do with your suggestion.

What do you anticipate will happen when the merge field (formatted as you suggested), encounters data which is not a time? I have a few fields in my main mail merge document that could be text or time.

Don't you find it odd that I do not have any of these issues, using the same VBA based application, the same data, and the same reports on my home computer with Office 2007, and that it's only the time values affected ... not formatted dates.  :Confused:

----------


## macropod

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.

----------

