How to use VBA to say 'Yes' to any SQL prompts in word?

Question

I'm using a macro, launched from a word document, which opens, executes, and closes a series of other documents, each of which contains a macro that executes a mail merge. The code works fine, but each time the new document is opened, I am given an SQL prompt, asking if I want to put the data into my document from the datasource.

I can just say 'Yes' to all of these dialog boxes, but

a) I would like to automate this to make it easier
b) Other users might also use this document, and I don't want to risk them breaking things by selecting 'No' because they are confused

Using the following thread I managed to put code in that selects the 'default' option for all message boxes, BUT it doesn't work because, for me, the default option for the SQL prompt is 'No'.

excel VBA to Automatically select Yes when prompted during mail merge

(I know you can also disable the prompt by going into the registry but that's not an option for me as I'll need it to work on many computers and I can't edit the registry on all of them for various reasons).

Here's the code at the moment (file paths redacted):

Sub castingbookmaster()
    '
    ' castingbookmaster Macro
    '
    '
        Dim tmp as Long
        tmp = Documents.Application.Displayalerts
        Documents.Application.Displayalerts = 0
        ChangeFileOpenDirectory "Y:\zzz\"
        Documents.Open FileName:= _
            "Y:\zzz\Mail Merge - All Active Scripts, Alphabetical.docm" _
            , ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
            PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
            WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
            wdOpenFormatAuto, XMLTransform:=""
        Application.Run MacroName:="CastingBook1"
        ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
        ChangeFileOpenDirectory "Y:\zzz\"
        Documents.Open FileName:= _
            "Y:\zzz\Mail Merge - Theatre, Active.docm", _
            ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
            PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
            WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
            wdOpenFormatAuto, XMLTransform:=""
        Application.Run MacroName:="theatre"
        ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
        ChangeFileOpenDirectory "Y:\zzz\"
        Documents.Open FileName:= _
            "Y:\zzz\Mail Merge - UK Casting Directors.docm", _
            ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
            PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
            WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
            wdOpenFormatAuto, XMLTransform:=""
        Application.Run MacroName:="UKcastingdirectors"
        ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
        ChangeFileOpenDirectory "Y:\zzz\"
        Documents.Open FileName:= _
            "Y:\zzz\Mail Merge - US Casting.docm", _
            ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
            PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
            WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
            wdOpenFormatAuto, XMLTransform:=""
        Application.Run MacroName:="UScasting"
        ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
        Documents.Application.DisplayAlerts = tmp
        MsgBox "All Casting Books have been updated."
End Sub

So, I either need

a) a way of setting the default value of the SQL prompt to 'Yes', then allowing DisplayAlerts = 0 to take care of it
b) a way of automatically selecting 'Yes' to the SQL prompt

If anyone could help it would be much appreciated.

Did you try to change the key value in RegEdit as suggested in the post you linked? — R3uK
Hi, thanks for the suggestion - I was aware that was a solution but I need this to work on a number of computers, and some of them I won't be able to change the registry on, so ideally I need a solution that can be kept within the documents. — J. Thompson
If need be, you can change Registry values from VBA : slipstick.com/developer/… — R3uK
Remind me-what happens if you save each of your documents without the data source attached, but modify the VBA code in each document to do the appropriate OpenDataSource? (You also have to make sure that people do not resave the documents with the data source attached). — user1379931
@bibadia great idea - thank you so much. Have just implemented this and it works a treat. I included a line at the end of each macro that opened a datasource that closed it at the end by setting {ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument} so that it can't be saved with datasource attached. — J. Thompson

J. Thompson J. Thompson · Accepted Answer · 2015-11-23T12:10:00

save each of your documents without the data source attached, but modify the VBA code in each document to do the appropriate OpenDataSource

How to use VBA to say 'Yes' to any SQL prompts in word?

1 Answers