Excel wait for Find Replace dialog box in Word

[JasonC]

I have a macro in Excel which opens up a Word doc then updates data to the
doc. I want to use find and replace to update some text. Rather than
replace all, I am using the find replace dialog box, so the user can check
each found text.

The trouble is, the code keeps executing while the dialog box is open in
word, and some of the subsequent steps crash when the dialog box is still
open. I can't move this code to the end either. Is there a way to tell
Excel to wait while the dialog box in Word is still open? See below for the
stripped down version of my sub.

Sub temp_Update_Document()
Dim docpath As String
Dim wdapp, objdoc As Object

'open word doc
docpath = "C:\Test Find & Replace.doc"
Set wdapp = CreateObject("Word.Application")
wdapp.Visible = True
Set objdoc = wdapp.Documents.Add(docpath)

'set the find and replace text
With wdapp.Selection.Find
.Text = "April"
.Replacement.Text = "May"
End With

'open the find & replace window
wdapp.Application.CommandBars("Edit").Controls("Replace...").Execute
'I want Excel to be wait here, instead of continuing to execute the code
End Sub

 

[Peter T]

Try this,

' with the declarations
Const wdDialogEditReplace As Long = 117&

'code
' comment the execute line
wdapp.Dialogs(wdDialogEditReplace).Display 10000

This should display the dialog, wait until dismissed by user or timeout of
10 seconds (adjust to suit). You might want to precede it with this -
AppActivate wdapp.Caption

In passing, change

Dim wdapp, objdoc As Object

to
Dim wdapp as Object, objdoc As Object

Regards,
Peter T

PS, not sure when the Display method was introduced, it's in 2003 but might
want to check if working with older versions.

 

[JLGWhiz]

Hi Peter, I normally use Show to get the dialog box to display. I couldn't
find a display method for it in the help file. Am I confused?

 

[Peter T]

Indeed could do this -

wdapp.Dialogs(wdDialogEditReplace).Show

However as the Display method exists in Word (shame it's n/a in Excel) might
as well take advantage of its Timeout feature. From Word-VBA help -

"Display Method
Displays the specified built-in Word dialog box until either the user closes
it or the specified amount of time has passed. Returns a Long that indicates
which button was clicked to close the dialog box."

Be sure to look in Word-VBA. If you can't find it what version of Word are
you looking in.

Regards,
Peter T

 

[JLGWhiz]

Oh! I missed the part about wdapp. Another senior moment. Put two
software engineers in a room on the same project and get three ways of doing
the same thing. Put them on different projects and guarantee that there
will be no semblence of similarity for methods to produce the same result.