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
LVL 1
cErasmusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
wdApp.DisplayAlerts = 0 'wdAlertsNone

Open in new window

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

Regards
0
cErasmusAuthor Commented:
Hi Rgonzo1971

Sorry but that does not work

E
0
ShumsDistinguished Expert - 2017Commented:
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

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
cErasmusAuthor Commented:
Hi Shums this also does not work
0
ShumsDistinguished Expert - 2017Commented:
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

0
cErasmusAuthor Commented:
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
0
cErasmusAuthor Commented:
Is there a way to tell word to "click" the Don't Save button?
0
GrahamSkanRetiredCommented:
Try specifically closing the documents before the Application.Quit

    wdDoc.Close wdDoNotSaveChanges
    .ActiveDocument.SaveAs Sheets("Program").Range("B4").Value & "Rebate Letters For " & Sheets("MergeSheet").Range("U2").Value & ".docx"
    .ActiveDocument.Close wdDoNotSaveChanges
    .Quit

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsDistinguished Expert - 2017Commented:
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

1
cErasmusAuthor Commented:
Thank you very much GrahamSkan
0
cErasmusAuthor Commented:
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
0
ShumsDistinguished Expert - 2017Commented:
No worry Elmo. Your problem got solved, that matters.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.