Avatar of cErasmus
cErasmus
Flag for Namibia asked on

Stop display of alerts in Word via Excel

Hi All

I have a piece of code that opens a word document and performs a mail merge. When it is done i want to close the template without saving. I have added
Application.DisplayAlerts = wdAlertsNone
but it does not seem to be working or i'm not using it correctly. There might also be another way to achieve this. Below is the code that i'm using.
Private Sub cmdMerge_Click()
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

On Error GoTo MyError

PrepareData
DoMerge

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

Exit Sub

MyError:
MsgBox Err.Description
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Word.Application.DisplayAlerts = wdAlertsAll

End Sub

Sub DoMerge()
Dim appWd As Word.Application
Dim WdDoc As Word.Document
Dim strBookFullName As String
Dim cell As Excel.Range
Dim strWordPath As String
Dim txt As String

strBookFullName = ActiveWorkbook.FullName
strWordPath = Sheets("Program").Range("A2").Value
Set appWd = CreateObject("Word.Application")
appWd.Visible = True


With appWd
.Application.DisplayAlerts = wdAlertsNone
    Set WdDoc = appWd.Documents.Open(strWordPath)
    WdDoc.Activate
    WdDoc.MailMerge.OpenDataSource Name:=(strBookFullName), _
    ReadOnly:=True, LinkToSource:=0, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:="", SQLStatement:="SELECT * FROM `MergeRange`", SQLStatement1:=""

    'finds text in word doc and replace with merge field
    For Each cell In Range("MergeText")
            txt = cell.Offset(0, 1).Value
            .ActiveDocument.MailMerge.Fields.Add Range:=WdDoc.Bookmarks(cell.Offset(0, 2).Value).Range, Name:=txt & cell.Offset(0, 3).Value
    Next cell
'.Application.DisplayAlerts = wdAlertsNone
    .ActiveDocument.MailMerge.Execute
    .ActiveDocument.SaveAs Sheets("Program").Range("B4").Value & "Rebate Letters For " & Sheets("MergeSheet").Range("U2").Value & ".docx"
    .Quit
.Application.DisplayAlerts = wdAlertsAll
End With
    Sheets("MergeSheet").Delete
    ThisWorkbook.Sheets("GoodmanAmana").AutoFilterMode = False
End Sub

Open in new window


Thanks in advance
Elmo
Microsoft ExcelMicrosoft WordMicrosoft OfficeVBA

Avatar of undefined
Last Comment
Shums Faruk

8/22/2022 - Mon
Rgonzo1971

Hi,

pls try
wdApp.DisplayAlerts = 0 'wdAlertsNone 

Open in new window

SInce you use late binding it does not know what wdAlertsNone is  

Regards
cErasmus

ASKER
Hi Rgonzo1971

Sorry but that does not work

E
Shums Faruk

Try changing as such:
Application.DisplayAlerts = False
    .ActiveDocument.MailMerge.Execute
    .ActiveDocument.SaveAs Sheets("Program").Range("B4").Value & "Rebate Letters For " & Sheets("MergeSheet").Range("U2").Value & ".docx"
    .Quit
Application.DisplayAlerts = True

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
cErasmus

ASKER
Hi Shums this also does not work
Shums Faruk

and this:
With appWd
    Application.DisplayAlerts = False
    Set WdDoc = appWd.Documents.Open(strWordPath)
    WdDoc.Activate
    WdDoc.MailMerge.OpenDataSource Name:=(strBookFullName), _
    ReadOnly:=True, LinkToSource:=0, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:="", SQLStatement:="SELECT * FROM `MergeRange`", SQLStatement1:=""

    'finds text in word doc and replace with merge field
    For Each cell In Range("MergeText")
            txt = cell.Offset(0, 1).Value
            .ActiveDocument.MailMerge.Fields.Add Range:=WdDoc.Bookmarks(cell.Offset(0, 2).Value).Range, Name:=txt & cell.Offset(0, 3).Value
    Next cell

    .ActiveDocument.MailMerge.Execute
    .ActiveDocument.SaveAs Sheets("Program").Range("B4").Value & "Rebate Letters For " & Sheets("MergeSheet").Range("U2").Value & ".docx"
    .Quit
    Application.DisplayAlerts = True
End With

Open in new window

cErasmus

ASKER
Shums
I had it like this previously and it also does not work also this causes another alert in Excel when i want to delete the MergeSheet
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cErasmus

ASKER
Is there a way to tell word to "click" the Don't Save button?
ASKER CERTIFIED SOLUTION
GrahamSkan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Shums Faruk

Try this:
With appWd
.Application.DisplayAlerts = wdAlertsNone
    Set WdDoc = appWd.Documents.Open(strWordPath)
    WdDoc.Activate
    WdDoc.MailMerge.OpenDataSource Name:=(strBookFullName), _
    ReadOnly:=True, LinkToSource:=0, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:="", SQLStatement:="SELECT * FROM `MergeRange`", SQLStatement1:=""

    'finds text in word doc and replace with merge field
    For Each cell In Range("MergeText")
            txt = cell.Offset(0, 1).Value
            .ActiveDocument.MailMerge.Fields.Add Range:=WdDoc.Bookmarks(cell.Offset(0, 2).Value).Range, Name:=txt & cell.Offset(0, 3).Value
    Next cell
    .ActiveDocument.MailMerge.Execute
    .ActiveDocument.SaveAs Sheets("Program").Range("B4").Value & "Rebate Letters For " & Sheets("MergeSheet").Range("U2").Value & ".docx"
    WdDoc.Close False
    Set WdDoc = Nothing
    appWd.Quit False
End With
Set appWd = Nothing
.Application.DisplayAlerts = wdAlertsAll
Application.DisplayAlerts = False
    Sheets("MergeSheet").Delete
Application.DisplayAlerts = True
ThisWorkbook.Sheets("GoodmanAmana").AutoFilterMode = False

Open in new window

cErasmus

ASKER
Thank you very much GrahamSkan
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
cErasmus

ASKER
Sorry Shums only saw your answer now. I have run it and it also works i'm going to go with Grahams though. Thank you
Shums Faruk

No worry Elmo. Your problem got solved, that matters.