Word 2013: Problem making connection to Merge from Excel 2013

Hi Experts,

Can anyone suggest the best way to make a connection to Excel 2013 from Word 2013 to do a mail merge?   We are able to connect using older versions of Office using DDE.  What happened in the upgrade and what should I change?  I can use Access if needed.

There's so many connections types I'm really confused. Using Windows 7 if that matters.

Priority is that the formatting is preserved.  Thanks for your help.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Since about  Office 2000, the default connection method is ODBC.

From our point-of-view the main difference between the methods is that DDE takes account of the local data formatting that the default application uses.

ODBC just looks at the raw data and ignores any display formatting that the application has requested. This reflects a general move to separate datasource types from any particular application. Remember that Access (JET) databases can be used without having the Access application installed at all.

I suggest that you move, application by custom application, to using ODBC. You can use format switches in mail merge to define the format there, rather than be dependent on the formatting as chosen in the Access, Excel  or any other application
thutchinsonAuthor Commented:
Very interesting background and helps me understand. Thank you.

Our admin always told me that if she didn't use DDE then she would lose all her formatting.  I will have to learn about these "format switches" in Word because I'm not familiar.  Her DDE connections wouldn't work any more after I gave her a new machine with Office 2013.  Now I'm having problems making any sort of connection. That's why I initiated this question.  I'm looking for some guidance on the selection choices as I go through the wizard.
The Mail Merge wizard reminds you of the choices that you can make.
If the option: 'Confirm file format on open' is not set, the merge will use ODBC. If it is set, you will get the chance of choosing the transfer method when you select a datasource.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

thutchinsonAuthor Commented:
I still can't make a connection.  This should be simple.  Word 2013 Mail Merge. Data Source is Excel 2013 file.  I formatted the data block as a table.  I tried every option as I worked through the wizard.
I still get the error:   "The data source contains no visible tables."
thutchinsonAuthor Commented:
There is a very good discussion of the problem here:


I was able to connect to DDE if I opened Excel 2013 before I ran the wizard. I still got errors indicating that the data source could not open but the merge worked.
I know DDE is very old technology but how else to get number formats into a Word doc?
I found the switch \* $,#.00 and it looked OK.  The drawback:  I'd have to visit every merge field in every Word mail merge doc to add the switch.  Simply inconceivable that this problem has never been addressed.
Does anyone know of a way to use an ODBC connection or OLE DB connection (neither produce errors) and still preserve the number formats from Excel?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
In theory, it should be fairly simple to devise a macro that will open a document, find its datasource, open that, and to set the switches for the merge fields according to the formatting in the workbook.
I'll have a try, though it might take a while (it's nearly my bedtime).
Here is a Word macro that tries to do what I was saying. It steps through a folder looking for mailmerge main documents with an Excel sheet as the datasource. It checks the  mailmerge fields and matches the name to text in the first (header) row of the sheet. It then examines the NumberFormat of the cell in the second row and adds an appropriate field switch to the merge field. As it stands, it is only functional for the Currency format, but it could be tweaked. The processed document is saved to another folder.

You will have to change the hard-coded document folder names.
Sub AddFieldSwitches()
    Dim strDocInFolder As String
    Dim strDocOutFolder As String
    Dim strDocFile As String
    Dim strExcelFile As String
    Dim strDataSource As String
    Dim strSheet As String
    Dim doc As Word.Document
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim strSQLParts() As String
    Dim fld As Word.Field
    Dim strFieldParts() As String
    Dim strFormat As String
    Dim r As Integer
    Dim c As Integer
    Dim f As Integer
    'instructions to supress security warning when opening a mailmerge document
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    strDocInFolder = "C:\Users\Graham Skan\Documents"
    strDocOutFolder = "C:\Users\Graham Skan\Documents\switches"
    strDocFile = Dir(strDocInFolder & "\*.docx")
    Application.ScreenUpdating = False
    Do Until strDocFile = ""
        Set doc = Documents.Open(strDocInFolder & "\" & strDocFile)
        If doc.MailMerge.MainDocumentType <> wdNotAMergeDocument Then
            If Right(doc.MailMerge.DataSource.Name, 5) = ".xlsx" Then
                strSQLParts = Split(doc.MailMerge.DataSource.QueryString, "`") 'SELECT * FROM `Sheet1$`
                If UBound(strSQLParts) = 2 Then '
                    strSheet = Left(strSQLParts(1), Len(strSQLParts(1)) - 1) 'drop trailing dollar character
                    Set xlWbk = xlApp.Workbooks.Open(doc.MailMerge.DataSource.Name, , True)
                    For Each fld In doc.Fields
                        If fld.Type = wdFieldMergeField Then
                            strFieldParts = Split(Trim(fld.Code.Text), " ")
                            If UBound(strFieldParts) = 1 Then  'no switch already in the mergefield
                                Set xlWks = xlWbk.Sheets(strSheet)
                                c = 1
                                Do Until xlWks.Cells(1, c).Value = ""
                                    If xlWks.Cells(1, c).Value = Replace(strFieldParts(1), """", "") Then 'this column matches the mergefield name
                                        strFormat = xlWks.Cells(2, c).NumberFormat
                                        Select Case strFormat
                                            Case "General" 'no switch needed
                                            Case "$#,##0.00" 'could add other formats here if the NumberFormat text is the same as the Switch text
                                                ReDim Preserve strFieldParts(3)
                                                strFieldParts(2) = "\#"
                                                strFieldParts(3) = strFormat
                                                fld.Code.Text = Join(strFieldParts, " ")
                                            Case Else
                                                'stop 'Some formats might need special treatment
                                        End Select
                                        Exit Do
                                    End If
                                    c = c + 1
                            End If
                        End If
                    Next fld
                    xlWbk.Close False
                    doc.SaveAs strDocOutFolder & "\" & strDocFile
                    MsgBox "Cannot extract Sheet name"
                End If 'sheet name in sql?
            End If '.xlsx datasource?
        End If 'mail merge doc?
        doc.Close wdDoNotSaveChanges
        strDocFile = Dir()
    Application.ScreenUpdating = True
End Sub

Open in new window

thutchinsonAuthor Commented:
Wow, that's way above the call of duty, Graham!  Thank you for that code.  I'll test it as soon as I get a chance.  This isn't an urgent project but we have a bunch of complex letters with dozens of merge fields each.  If your macro works, it will be gold.
thutchinsonAuthor Commented:

From what I found scrolling for answers, Word 2013 has an issue opening other applications-even in the Office suite.  I was able to connect to the Excel data source only if I opened Excel first.
Since the number formats from the Excel data source can't be preserved/carried over to the Word doc using OLE or ODBC connections then you have to apply switches. This was a problem for me because we have dozens of Word docs with as many fields each.  I look forward to trying Graham's macro.  Thank you Graham.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.