cErasmus

asked on 3/14/2017

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

Last Comment

Shums Faruk

8/22/2022 - Mon

Rgonzo1971

3/14/2017

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

3/14/2017

ASKER

Hi Rgonzo1971

Sorry but that does not work

E

Shums Faruk

3/14/2017

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

3/14/2017

ASKER

Hi Shums this also does not work

Shums Faruk

3/14/2017

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

3/14/2017

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

3/14/2017

ASKER

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

ASKER CERTIFIED SOLUTION

GrahamSkan

3/14/2017

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

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

3/14/2017

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

3/14/2017

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

3/14/2017

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

3/14/2017

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