Link to home
Create AccountLog in
Avatar of Elmo Erasmus
Elmo ErasmusFlag 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
Avatar of Rgonzo1971
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
Avatar of Elmo Erasmus

ASKER

Hi Rgonzo1971

Sorry but that does not work

E
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

Hi Shums this also does not work
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

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
Is there a way to tell word to "click" the Don't Save button?
ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

Thank you very much GrahamSkan
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
No worry Elmo. Your problem got solved, that matters.