PDA

View Full Version : Problem with my search/replace macro



sindre182
09-08-2011, 04:59 AM
I trying to use this macro in excell, but it only opens the document, and not replacing. Can someone please help me finding whats wrong? The goal is to get the macro to fing where it sais XXXXX in the document, and replace it with the numbers given in Replacement.Text..

Sub Find_and_Replace()
Dim appWD As Object
Dim docWD As Object
Set appWD = CreateObject("Word.Application")
Set docWD = appWD.documents.Open("M:\Fugro_filer\testing\XXX.02 Project_manual_template.doc")
appWD.Visible = True
With docWD.Content.Find
.Text = "XXXXX"
.Replacement.Text = "123"
.Forward = True
.Wrap = 1
.Execute Replace:=2
End With
End Sub

Apps
09-08-2011, 05:45 AM
Hi,

tested on mine and although the application is now visible, you also need to make the document Active :

appWD.Visible = True
'activate document
docWD.Activate
'then do find+replace
With docWD.Content.Find


then it worked fine for me.

Kenneth Hobs
09-08-2011, 06:09 AM
I did not need to activate in mine since that is inferred.

Sub Test_SearchReplaceInDoc()
SearchReplaceInDoc "x:\MSWord\SearchReplace\SearchReplaceInDoc.doc", "XXXXX", "123", True, False
End Sub

'http://www.vbaexpress.com/forum/showthread.php?t=38958
Sub SearchReplaceInDoc(doc As String, findString As String, replaceString As String, _
Optional docVisible As Boolean = True, _
Optional closeDoc As Boolean = True)

Dim wdApp As Object, WD As Object, rn As Long

rn = ActiveCell.Row
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then Set wdApp = CreateObject("Word.Application")
On Error GoTo 0

If Dir(doc) = "" Then Exit Sub
Set WD = wdApp.Documents.Open(doc)
wdApp.Visible = docVisible

With WD.Content.Find
.Text = findString '"XXXXX"
.Replacement.Text = replaceString '"123"
.Forward = True
.Wrap = 1
.Execute Replace:=2
End With

If closeDoc Then
Set WD = Nothing
Set wdApp = Nothing
End If
End Sub

Apps
09-08-2011, 06:36 AM
Hi Ken,

I agree, however when testing the original code in 2003 I experienced the same issue as Sindre was?
I have seen this before in Excel workbooks as well as Word so I usually add in an Activate just to be on the safe side in a belt and braces approach, especially when I have multiple files open at the same time and want to be sure the code is targeting the correct one.
:)

sindre182
09-08-2011, 09:21 AM
Thank you both for answering. That was helpfull.
The problem now is that I don't seem to be able to change were it originally stood "xxx" in the document. The macro is only able to change were i write "xxx" on the pages in the document. Does anyone know what might be wrong?

Apps
09-08-2011, 11:02 AM
Thank you both for answering. That was helpfull.
The problem now is that I don't seem to be able to change were it originally stood "xxx" in the document. The macro is only able to change were i write "xxx" on the pages in the document. Does anyone know what might be wrong?

Hi Sindre,

Apologies I don't quite understand - could you post an example if possible?
In your original code any instance of "XXXXX" would have been replaced with "123" .

sindre182
09-08-2011, 01:00 PM
Hi Sindre,

Apologies I don't quite understand - could you post an example if possible?
In your original code any instance of "XXXXX" would have been replaced with "123" .

Sure. Well, there is some text in the document which someone has written as a template. That's where "XXXXX" is. I am able to edit them just by writing, but they are in text boxes.. When I run the macro it edits everywhere I have written "XXXXX" in the document, but not the "XXXXX" that originally is there..

Kenneth Hobs
09-08-2011, 01:24 PM
If it is in a textbox, that method will not change those. Post an example doc.

sindre182
09-08-2011, 02:14 PM
If it is in a textbox, that method will not change those. Post an example doc.

Here you have the file.. Actully, I want the script to replace XXX, xxx, XXXXX with 123. If there is a way the script can search for all of these it would be best..

Thank you so much!

6542

sindre182
09-10-2011, 01:40 PM
Please, does anyone know how to do this? Ken, have you checked out the document? I am still strugling with vba.. No one at my university knows any vb syntax.

Kenneth Hobs
09-10-2011, 01:56 PM
I did check into it and used a method recommended by someone else to iterate some ranges but it did not change some of your data. Even the manual method that the article talked about did not do it. I will see if I can iterate the shapes and try something there.

sindre182
09-19-2011, 01:43 AM
Still nothing? Anyone?