Automated Mail Merge from Excel VBA still failing - DDE related?

[Ark68]

Hi Friends ...

I continue to be challenged by the Excel VBA based automation of MS Word's mail merge feature. Here is my code again:

Option Explicit

    Dim objword As Object
    Dim odoc As Object
    Dim odoc2 As Object
    Const wdSendToNewDocument = 0
    Const wdSendToPrinter = 0
    Dim mypath As String

Sub merge()
    
    Dim fName As String
    Set objword = CreateObject("Word.Application")
    objword.DisplayAlerts = True
    fName = Sheets("varhold").Range("A37").Text
    Debug.Print fName
    Set odoc = objword.Documents.Open(fName)
    objword.Visible = True
    odoc.Application.Visible = True
    odoc.Mailmerge.Destination = wdSendToNewDocument
    odoc.Mailmerge.Execute
    Set odoc2 = odoc.Application.Documents("Catalog1")
    odoc.Close False
    mypath = "u:\Integrity12\Workorders\" & Format(Worksheets("varhold").Range("A26"), "ddd dd-mmm-yy")
    If Len(Dir(mypath, vbDirectory)) = 0 Then MkDir mypath
    odoc2.SaveAs mypath & "\" & (Worksheets("varhold").Range("A40").Value & ".docx")
    objword.DisplayAlerts = True
    'objword.Application.Quit True
    AppActivate "Microsoft Excel"
    
    Set objword = Nothing
    Set odoc = Nothing
    Set odoc2 = Nothing
End Sub

Purpose: From a toggle button click in a userform, this code opens the mail merge document referred to in cell A37 or defined sheet 'varhold'. The mail merge is then executed using the embedded SQL on the mail merge document in which data is extracted from an external Excel 2010 worksheet. The merged document is saved, and remains open (although minimized) in the taskbar for editting.

History:
Code was originally written for Office 2003. Initially struggled with date and time formatting issues in the merged document (despite Excel data being formatted appropriately, the merged data was always it's decimal equivalents even with the proper mergefield switches). This was resolved by using the DDE linking method between mail merge and Excel data. From initial manual testing (manually initiating the mail merge), the mail merge worked without significant flaw. Once the automation was introduced as explained above, significant problems began very similar to what I am experiencing now. (see the problem below)

An upgrade in MS Office to Office 2010 at school required me to revisit my entire EXCEL VBA application to amend any file extension references in my code. I was also forced to revisit my main mail merge documents to qualify them as docx extensions. Of course, I had to manually go through each and redefine the datasource. Each mail merge document was saved as a docx with the datasource now referring to the xmls excel datasource. Interestingly enough, after these changes, my application's automation of the mail marge worked well. It launched Word, opened the respective mail merge document, merged the information, saved it and displayed it. However ... I was back to the loss of time formatting in my merged document. Times were once again being displayed as decimals. Now, this is what I find most unusual. The same application, the same datasource, the same reports (without edit) worked flawlessly on my home computer using MS Office 2007. The automation worked flawlessly, and I had no time formatting issues. Basically, everything I had hoped for.

So, back to the forums I went looking for the help of the well seasoned and experienced and knowledgeable gurus. After a bunch of head scatching, it was suggested I redefine my datalink between Word and Excel using DDE. (I must have lost it in translation?) Anyway, I did as suggested, removing the existing datasource reference, and re-introducing it with a DDE connection. With this ... augh ... the problems started again! I was again experiencing very similar problems to that experienced in point 1 above. The resulting problem is below.

The Problem : From the application, when the user presses the toggle button to launch the macro, it hangs. There appears to be no activity. I'm assuming because Word displays the SQL prompt, it must be active, yet there is no evidence on the taskbar or desktop. It appears as though the datasource (MergeData.xlsx) is open. After a few minutes, Word displays a message "This task is taking longer than expected. Do you want to continue waiting?" The Word application now appears on the desktop empty.

YES = Another wait, same message as above. Continuing to selecting yes just repeats the same loop.

NO = Prompt: "File in use. MergeData.xlxs is locked for editing ...".
Select [READ ONLY]
Message "This task is taking longer than expected. Do you want to continue waiting?
[NO]
Word Message "Word could not re-establish a DDE connection to Microsoft Excel to complete the current task"
[OK]
Word Message "DR-RPL7.docx is a mail merge document. Word cannot find it's data source, U:\Integrity12\Reports\MergeData.xlsx.
[CLOSE] - no action to find data source taken ....
Word Message "The file DR-RPL7.docx cannot be opened because there are problems with the contents."
[DETAILS] Unspecified error Location: Part: /word/settings.xml, Line: 2, Column 2934
[OK]
Word Message "Word found unreadable content in DR-RPL7.docx. Do you want to recover ...."
[NO]
VBA Error message "Word could not re-establish a DDE connection to complete the current task"
[DEBUG]

Set odoc = objword.Documents.Open(fName)

Note: at this point, two instances of MergeData.xlsx are open ... one normal, one read only.

If I take out the automation component, and test by manually initiating the mail merge, here is what happens ...

Excel closed or open with MergeData.xlsx active or not.
Open mail merge main document (DR-RPL7.docx)
Word message: "Opening this document will run the following SQL command:" . Everything is correct.
[YES]
Excel application appears in taskbar, MergeData.xlsx is open.
DR-RPL7.docx opens with appropriate data being displayed.
Data merges accurately, the only oddity being the prompt the save changes to MergeData.xlsx on closing. (Don't know what would have changed).

My plea:
I hope someone can help me isolate and resolve the issue of trying to automate my mail merge portion of my application. This has been cross posted at http://www.excelforum.com/word-form...me-suddenly-displaying-as-decimals-again.html .

Thanks for your time.