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]

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]

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]

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]

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]

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]

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]

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]

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]

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.