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.
Thanks in advance
Elmo
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
Hi,
pls try
Regards
pls try
wdApp.DisplayAlerts = 0 'wdAlertsNone
Regards
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
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
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
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
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
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