Excel-Access-Word Mail Merge headache

[20Euclid17]

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]

In that case, change: wdPrintPreview to wdNormalView

 

[20Euclid17]

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]

Try exiting your account & re-starting.

 

[20Euclid17]

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]

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

 

[20Euclid17]

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]

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