Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel wait for Find Replace dialog box in Word
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("Re place...").Execute 'I want Excel to be wait here, instead of continuing to execute the code End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel wait for Find Replace dialog box in Word
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. "JasonC" wrote in message ... 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("Re place...").Execute 'I want Excel to be wait here, instead of continuing to execute the code End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel wait for Find Replace dialog box in Word
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" <peter_t@discussions wrote in message ... 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. "JasonC" wrote in message ... 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("Re place...").Execute 'I want Excel to be wait here, instead of continuing to execute the code End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel wait for Find Replace dialog box in Word
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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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. "JasonC" wrote in message ... 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("Re place...").Execute 'I want Excel to be wait here, instead of continuing to execute the code End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel wait for Find Replace dialog box in Word
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. "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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. "JasonC" wrote in message ... 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("Re place...").Execute 'I want Excel to be wait here, instead of continuing to execute the code End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace in Word from Excel | Excel Programming | |||
Find/Replace macro from excel to word | Excel Programming | |||
FIND AND REPLACE DIALOG BOX | Excel Discussion (Misc queries) | |||
Excel Find/Replace Dialog Box | Excel Discussion (Misc queries) | |||
How can I use find and replace to delete a word in Excel? | Excel Discussion (Misc queries) |