troubleshooting Question

Stop display of alerts in Word via Excel

Avatar of cErasmus
cErasmusFlag for Namibia asked on
Microsoft OfficeMicrosoft ExcelMicrosoft WordVBA
12 Comments1 Solution913 ViewsLast Modified:
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

Thanks in advance
Elmo
ASKER CERTIFIED SOLUTION
GrahamSkan
Retired
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros