VBA mailmerge Excel to Word Problems

[MikeDun]

Hello,

I am attempting to import data from an Excel spreadsheet into a pre-made Word document using a macro in Excel.

The idea with these files is that they will be stored in a master folder and copied out by the Employees who will rename them according to the project they are working on (each project requires each of these files to be included with it).

Basically I would like the location of the data that the Word file I am working on to change dynamically based on the employee changing the location and name of the sheet.

I used the following code to attempt this:

Sub RunMailMerge() 'Will merge data on INPUTSsheet into customized document with fields for all data that needs to be entered

    Dim wdOutputName, wdInputName, strWorkbookName As String 'file path names
    wdOutputName = ThisWorkbook.Path & "\OutputDoc.doc") 'document to output
    wdInputName = ThisWorkbook.Path & "WordInputDoc.doc" 'doc with fields used as base

    ' open the mail merge layout file
    Dim wdDoc As Object 'declare object for word doc
    Set wdDoc = GetObject(wdInputName, "Word.document") 'set object as Word document used as base
    wdDoc.Application.Visible = True

    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name 'the string will use the location of the current Excel file used to define the database
 
 With wdDoc.MailMerge 'run through mailmerge commands for input word document
    .OpenDataSource _
            Name:=strWorkbookName, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=wdOpenFormatAuto = 0, _
            Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
            SQLStatement:="SELECT * FROM [IPNUTS$]" 'selects the data source used for the mailmerge - should be this spreadsheet under the INPUTS tab
    .MainDocumentType = wdFormLetters
    .Destination = wdSendToNewDocument 'merges results to new document
    .SuppressBlankLines = True
    .Execute Pause:=False 'report errors in a new document if any occur
    End With

    ' show and save output file
    wdDoc.Application.Visible = True
    wdDoc.Application.ActiveDocument.SaveAs wdOutputName 'save as a new file - named after the output name given above
    On Error Resume Next
    
    ' cleanup
    wdDoc.Close SaveChanges:=False 'close out original base doc
    Set wdDoc = Nothing 'clear object from memory

End Sub

It worked beautifully at first - change data in the spreadsheet, click the export button and a new word file is saved with the updated fields. However during testing it started giving me a variety of errors including:

"An operation cannot be completed because of database engine errors"

Which would bring up the Data Link properties tab in Word. I noticed in the source field it indicates the path to the original spreadsheet (that would normally be copied from) rather than the spreadsheet that is currently being used. This is despite me specifying that the data source is this path/this workbook etc.

Why wouldn't it use the spreadsheet currently being used as the source like indicated?

In addition, the 'Select Table' prompt suddenly started appearing after going through all these errors - but it is blank and triggers an error in code when you close/cancel/click OK. 'Run-time error '5922': Word was unable to open the data source.' when clicking OK or 'Run-time error 4198: command failed' when cancelling or closing.

The problem seems to be with the .OpenDataSource method of mailmerge.

This whole thing is really screwy - I've looked up all these errors but can't seem to find anything that will help my code work - maybe the whole thing needs to be redone? It seemed so simple but obviously something is messed up.

I'd appreciate any input your community could provide. Thank you for your time and effort.

Running Word/Excel 2010 on Windows 7 Enterprise.

 

[Macropod]

There are some fairly obvious errors in your variable assignments for wdOutputName & wdInputName. Try:

Dim wdOutputName As String, wdInputName As String, strWorkbookName As String 'file path names
wdOutputName = ThisWorkbook.Path & "\OutputDoc.doc" 'document to output
wdInputName = ThisWorkbook.Path & "\WordInputDoc.doc" 'doc with fields used as base

and:

strWorkbookName = ThisWorkbook.FullName 'the string will use the location of the current Excel file used to define the database

Also, you need to ensure the Word document is NOT opened as a mailmerge main document (even if it has been saved that way - which, ideally, you wouldn't). You can force Word to treat it as an ordinary document upon opening by replacing:

    Dim wdDoc As Object 'declare object for word doc
    Set wdDoc = GetObject(wdInputName, "Word.document") 'set object as Word document used as base
    wdDoc.Application.Visible = True

with:

    Dim wdApp As Object, wdDoc As Object
    Set wdApp = CreateObject("Word.Application")
    wdApp.DisplayAlerts = False
    Set wdDoc = wdApp.Documents.Open(wdInputName, False, True, False, , , , , , , , True) 'set object as Word document used as base

 

[MikeDun]

Thank you for the reply.

I have edited the code as per your suggestions. It has definitely improved the performance of the Word document on open which is excellent because now there are no more database engine errors popping up.

What happens now when running the code is that Word still opens with the 'Select Table' prompt. But it shows nothing even after checking the other options. I am still getting the same two errors I described initially: 'Run-time error '5922': Word was unable to open the data source.' when clicking OK or 'Run-time error 4198: command failed' when cancelling or closing.

Any ideas as to what might be causing this?

Thanks again for your help.

 

[Macropod]

Well, if we look at your SQLStatement, we see:
SQLStatement:="SELECT * FROM [IPNUTS$]"
What do you reckon the chances are the sheet name is IPNUTS?

 

[MikeDun]

That's a good catch! However, I altered some names for clarity when I made the post so it is exclusive to that code.

Sorry about the mistake regardless. I double checked and it is spelt correctly in my file.

 

[Macropod]

If you're getting a Select Table prompt, that indicates that your SQLStatement is faulty. Assuming INPUTS is the sheet name, you might try:
SQLStatement:="SELECT * FROM `INPUTS$`"
but that shouldn't really make any difference.

Also, are you running this from a saved workbook?

BTW, before exiting Word, you should insert:
wdApp.DisplayAlerts = True

 

[MikeDun]

It turns out that my original code also had a typo for the sheet in the SQL code. I may need to get my eyes checked apparently.

Anyway it worked after that, and your adjustments to the code have been a big help.

Thanks for your assistance.

I've noticed it mysteriously doubles the length of the generated Word doc - it only started doing this after I created/inserted all of the fields. The first half is the document with all the fields updated correctly and the duplicated part is the document without any fields at all, appended to the generated doc. What's strange is if I reduce the amount of fields being used this error suddenly does not occur. It only causes this duplication upon going over 47 fields.

Thanks again for your help.

 

[Macropod]

I've noticed it mysteriously doubles the length of the generated Word doc - it only started doing this after I created/inserted all of the fields. The first half is the document with all the fields updated correctly and the duplicated part is the document without any fields at all, appended to the generated doc. What's strange is if I reduce the amount of fields being used this error suddenly does not occur. It only causes this duplication upon going over 47 fields.

This suggests you're doing something that appends the mailmerge main document's content to the output document; it doesn't have anything to do with the number of fields, though - 47 is a trivial number.

 

[Macropod]

Cross-posted at: http://answers.microsoft.com/en-us/...-to-word/dd168837-c9bb-4c02-8612-6f523f551e87
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters