1

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.

Community
  • 1
  • 1
J. Thompson
  • 19
  • 1
  • 7
  • 1
    Did you try to change the key value in RegEdit as suggested in the post you linked? – R3uK Nov 19 '15 at 10:51
  • 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 Nov 19 '15 at 11:49
  • If need be, you can change Registry values from VBA : http://www.slipstick.com/developer/read-and-change-a-registry-key-using-vba/ – R3uK Nov 19 '15 at 11:56
  • 1
    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). –  Nov 20 '15 at 07:37
  • @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 Nov 20 '15 at 14:12
  • If you solve a problem, add your solution as an answer so you can later mark it accepted. We don't mark titles as "SOLVED" here. – Joel Coehoorn Nov 20 '15 at 14:22
  • @J. Thompson: Glad it worked. I really couldn't remember for sure. So I suggest you post your own Answer and accept it (that is allowed here). As a final comment, more recent versions of Windows Word (not Mac Word) have a ActiveDocument.MailMerge.DataSource.Close which probably has the same effect, but IMO it's a bit more obvious what it is supposed to do (for anyone who has to maintain the code). –  Nov 20 '15 at 16:13

1 Answers1

0

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

J. Thompson
  • 19
  • 1
  • 7