# Excel-Access-Word Mail Merge headache



## 20Euclid17 (Sep 5, 2017)

I currently have an excel sheet which serves as the input to a mail merge for a word doc. I want to write code so that at the push of a button, the excel sheet will save and the merged word document will open. However, for some reason when opening the merged doc like this disconnects the data source, so all my merge fields go blank and don't work. Note this doesn't occur when saving and closing the excel sheet, and then opening the word document manually. Here is my code so far (it's really simple):

Sub Button1_Click()

ActiveWorkbook.Save


Set wordapp = CreateObject("word.Application")
wordapp.Documents.Open "C:\Desktop\Template.doc"
wordapp.Visible = True


End Sub

I was hoping someone could explicitly tell me how to establish/re-establish the data connection from the word document to the access database I've linked the excel sheet to; either in VBA code for the word doc or in the code for the button I push in excel.

I've found half solutions and similar cases and have experimented, but have failed to make this work for my purposes. I'm not a programmer, I just want to add this time saving convenience to my auto-template generator. Any help is appreciated. Thanks


----------



## Macropod (Sep 5, 2017)

There's not enough code there to know what the problem is. For some code to get you started, see: https://www.mrexcel.com/forum/gener...e-excel-2010-vba-post4872807.html#post4872807


----------



## 20Euclid17 (Sep 5, 2017)

As I understand it from google searches, this is a common issue with mail merge docs when you open them and their source excel file is also open. Word is designed to sever it's data connection for security purposes (from what I read). I don't really understand it, I just know that when I open my word doc (either through my button or when excel is still open) the connection is severed and none of the merge fields show up.

I'll look through the link you sent, but again I'm a beginner with VBA so I probably will need a bit more help. Let me know any info you need. This word doc has 100+ types of fields used multiple times, in my excel sheet on the second sheet the first row lists mergefield names, the second row their values. As I said they're linked through access. Thanks


----------



## Macropod (Sep 5, 2017)

The code in the link I posted is written for running from the data source - an Excel workbook - and your 'disconnects the data source' concern has never arisen with such code (I've provided solutions coded like that numerous times, e.g. https://www.excelforum.com/word-for...find-the-mail-merge-vba-code.html#post4385951 & http://www.msofficeforums.com/mail-merge/20461-cant-merge-mail-excel-2.html#post61768). That said, I fail to see why you're exporting the data to Excel and trying to run a mailmerge from there. Why not do it all from Access?


----------



## 20Euclid17 (Sep 6, 2017)

Sorry if I wasn't clear, the input is in excel which I linked to an access database, and it's mail merged to a word doc.

I'll give your code a try and let you know how it turns out. Thanks!


----------



## 20Euclid17 (Sep 6, 2017)

Ok, so I took your code from the MSOfficeForums link, made a few small changes, tried running it, and got "Compile error: User-defined type not defined". It highlighted the "wdapp As New Word.Application" in debugger. For clarity, here is the code I tried to run:

Sub Button1_Click()


ActiveWorkbook.Save


Dim strWorkbookName As String
    strWorkbookName = ThisWorkbook.FullName
    Dim wdapp As New Word.Application
    Dim wddoc As Word.Document
    With wdapp
        'Disable alerts to prevent an SQL prompt
        .DisplayAlerts = wdAlertsNone
        'Open the mailmerge main document
        Set wddoc = .Documents.Open(ThisWorkbook.Path & "\Template.doc", _
            ConfirmConversions:=False, ReadOnly:=True, AddToRecentFiles:=False)
        With wddoc
            With .MailMerge
                'Define the mailmerge type
                .MainDocumentType = wdFormLetters
                'Connect to the data source
                .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
                  AddToRecentFiles:=False, LinkToSource:=False, _
                  Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "User ID=Admin;Data Source=strWorkbookName;" & _
                  "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
                  SQLStatement:="SELECT * FROM `Program$`", _
                  SubType:=wdMergeSubTypeAccess
                .SuppressBlankLines = True
                With .DataSource
                    .FirstRecord = wdDefaultFirstRecord
                    .LastRecord = wdDefaultLastRecord
                End With
                'Define the output
                .Destination = wdSendToNewDocument
                'Excecute the merge
                .Execute
                'Disconnect from the data source
                .MainDocumentType = wdNotAMergeDocument
            End With
            'Close the mailmerge main document
            .Close False
        End With
        'Restore the Word alerts
        .DisplayAlerts = wdAlertsAll
        'Display Word and the document
        .Visible = True
    End With
End Sub


----------



## Macropod (Sep 6, 2017)

The code uses early binding. Accordingly, you need to set a VBE reference to the Word library, via Tools|References.

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.


----------



## 20Euclid17 (Sep 6, 2017)

Ok, set VBE reference to library. Now I get this error: "Run-time error '4605': The OpenDataSource method or property is not available because this command is not available for reading."

This is the code the debugger highlighted:


```
.OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _                  AddToRecentFiles:=False, LinkToSource:=False, _
                  Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "User ID=Admin;Data Source=strWorkbookName;" & _
                  "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
                  SQLStatement:="SELECT * FROM `Program$`", _
                  SubType:=wdMergeSubTypeAccess
```


----------



## Macropod (Sep 6, 2017)

An online search suggests the problem is caused by the view the mailmerge main document has been saved in and, hence, is being opened in. Try inserting:
.ActiveWindow.View.Type = wdPrintPreview
after:
With wddoc


----------



## 20Euclid17 (Sep 6, 2017)

Added that, now it says "Run-time error '4605': The OpenDataSource method or property is not available because preview mode is active." and highlights the same lines of code.


----------



## 20Euclid17 (Sep 5, 2017)

I currently have an excel sheet which serves as the input to a mail merge for a word doc. I want to write code so that at the push of a button, the excel sheet will save and the merged word document will open. However, for some reason when opening the merged doc like this disconnects the data source, so all my merge fields go blank and don't work. Note this doesn't occur when saving and closing the excel sheet, and then opening the word document manually. Here is my code so far (it's really simple):

Sub Button1_Click()

ActiveWorkbook.Save


Set wordapp = CreateObject("word.Application")
wordapp.Documents.Open "C:\Desktop\Template.doc"
wordapp.Visible = True


End Sub

I was hoping someone could explicitly tell me how to establish/re-establish the data connection from the word document to the access database I've linked the excel sheet to; either in VBA code for the word doc or in the code for the button I push in excel.

I've found half solutions and similar cases and have experimented, but have failed to make this work for my purposes. I'm not a programmer, I just want to add this time saving convenience to my auto-template generator. Any help is appreciated. Thanks


----------



## Macropod (Sep 6, 2017)

In that case, change: wdPrintPreview to wdNormalView


----------



## 20Euclid17 (Sep 6, 2017)

Seemed promising at first, the code ran for a long time before giving this prompt: "Microsoft Excel is waiting for another application to complete an OLE action". If I hit ok it continues running, and then gives that error message again.


----------



## Macropod (Sep 6, 2017)

Try exiting your account & re-starting.


----------



## 20Euclid17 (Sep 6, 2017)

Restarted and tried again, got the same error message. 

Looked this issue up in a search, supposedly one way to fix it is to check the "Ignore other applications that use Dynamic Data Exchange (DDE)" in the advanced options. Tried this, when opening my excel file got error message "There was a problem sending the command to the program." Also checked my Excel add-ins, I don't have any checked currently.

The headache continues. Thanks for the help so far Macropod. Hopefully you or someone can save me


----------



## Macropod (Sep 6, 2017)

You might try repairing the Office installation (via Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change > Repair).


----------



## 20Euclid17 (Sep 11, 2017)

So I tried repairing the installation, after I tried again and same result. Looks like a dead end with this method.

Is there anyway I could write some script upon starting up my word doc? So I'd still have my button in excel:


```
Sub Button1_Click()

ActiveWorkbook.Save


'Open Word
'Set wordapp = CreateObject("word.Application")
'wordapp.Documents.Open "C:\Desktop\Template.doc"
'wordapp.Visible = True
End Sub
```

And then inside my word doc:

```
Private Sub Document_Open()
'Re-establish WordMerge data source code TBD
End Sub
```

I'm not sure what code to use to do this in Word, again any help is much appreciated. Thanks


----------



## 20Euclid17 (Sep 11, 2017)

Got it! I recorded a macro where I went and re-added my database; I stole that code and now everything loads without error. For reference, here is my final vba code in word:


```
Private Sub Document_Open()

    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Desktop\Database.accdb", ConfirmConversions _
        :=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
        Connection:= _
        "DSN=MS Access Database;DBQ=C:\Desktop\Database.accdb;DefaultDir=C:\Desktop\Database.accdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;" _
        , SQLStatement:="SELECT * FROM `Program`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeOther


End Sub
```

Thanks for the help


----------

