VBA in Microsoft Word

[gdspeare]

Does anyone have any code that will open all the files in a specified folder and then replace every instance of a certain word, resave and then move to the next file?

Thanks,

David

 

[purnima]

Hi,

You can try this code.


Sub find_replace_all_files()

Dim i as Integer

With Application.FileSearch
.FileName = "*.doc"
.LookIn = "C:\My Documents"
.Execute
If .FoundFiles.count <> 0 Then
For i = 1 To .FoundFiles.count
MsgBox .FoundFiles(i)
Documents.Open ("C:\My Documents\" & .FoundFiles(i))
ActiveDocument.TrackRevisions = True
ActiveDocument.Select
With Selection.Find
.Text = "Type the word you wish to find here"
.Replacement.Text = "Type the replacement word here"
.MatchWildCards=False
End With
Selection.Find.Execute Replace:=wdReplaceAll
ActiveDocument.Save
ActiveDocument.Close
Next i
End If
MsgBox "Macro execution complete."

End With

I have specified "C:\My Documents\" as the folder to look in. But you can replace it with whichever folder you want.

Hope this helps you.

Purnima.

 

[DougStroud]

Purnima,
I found your script and ran it. I ran into a error. Can you check this out and let me know if I have something wrong?
Debugger finds the first line as the error.

Sub find_replace_all_files()

-----------------------------------------------------------------------------------
Sub find_replace_all_files()

Dim i As Integer

With Application.FileSearch
.FileName = "*.doc"
.LookIn = "C:\My Documents"
.Execute
If .FoundFiles.Count <> 0 Then
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Documents.Open ("C:\My Documents\" & .FoundFiles(i))
ActiveDocument.TrackRevisions = True
ActiveDocument.Select
With Selection.Find
.Text = "Quiksilver"
.Replacement.Text = "Quicksilver!"
.MatchWildcards = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
ActiveDocument.Save
ActiveDocument.Close
Next i
End If
MsgBox "Macro execution complete."

End With

I am curious, to change the words to find and substitute, you have to enter them in the code or when the code runs, it stops and asks the user to input?
If not then can a user input box be put in?

Doug

 

[purnima]

Hi Doug,

Yes. The macro gives an error. But I have fixed it now. Also, regarding your question about an inputbox, it can be done that way. It would be a good idea to let the user specify the term to be found and replaced.

Here is the code modified accordingly.

Sub find_replace_all_files()

Dim i As Integer
Dim term As String, fTerm As String, rTerm As String

term = InputBox("Please specify the term to be found and its replacement, separated by a forward slash(/)." _
& vbCrLf & "Example:" & vbCrLf & "Quiksilver/Quicksilver!")

fTerm = Left(term, InStr(term, "/") - 1)
rTerm = Right(term, InStr(term, "/"))

With Application.FileSearch
.FileName = "*.doc"
.LookIn = "C:\My Documents"
.Execute
If .FoundFiles.count <> 0 Then
For i = 1 To .FoundFiles.count
Documents.Open (.FoundFiles(i))
ActiveDocument.TrackRevisions = True
ActiveDocument.Select
With Selection.Find
.Text = fTerm
.Replacement.Text = rTerm
.MatchWildcards = False
.Format=False
End With
Selection.Find.Execute Replace:=wdReplaceAll
ActiveDocument.Save
ActiveDocument.Close
Next i
End If
MsgBox "Macro execution complete."

End With

End Sub


Do let me know if this helps.

Purnima.

 

[DougStroud]

I replaced the code and I get the input box and upon filling it in and running it, I get a message that the macro ran. However, it does not fix the misspelled words.
I tried it when all files are closed and when the specific file w/ the misspelled words was open.
The macro runs very quickly and does not actually open any files at all.

Can you see if that happens on your end?

 

[purnima]

Hi Doug,

The macro does work on my comp. It opens files, looks for the specified term in it, replaces it, if found, and then saves and closes it. Maybe that is why you feel it does not open any files.

One reason why it did not make any replacements can be because of some Find options/settings which were set in a previous Find action.

Though I'll test the macro once again and let you know as and when I figure out the reason for this issue.

Purnima.

 

[Joey_R]

Open all files in a DIR and then change color & close fi

Hi there,

please advise on how to achieve the following.

I would like to open all the files in the MyDocuments folder and then change the colur of all the text and then close all the files.

Many thanks
Joey_R

 

[purnima]

Hi,
Here's your code:

Sub change_font_color()

Dim fileCntr As Integer

With Application.FileSearch
.FileName = "*.doc"
.LookIn = "C:\My Documents"
.Execute
If .FoundFiles.count <> 0 Then
For fileCntr = 1 To .FoundFiles.count
Documents.Open (.FoundFiles(fileCntr))
ActiveDocument.Select
Selection.WholeStory
Selection.Font.Color = wdColorBlue
ActiveDocument.Save
ActiveDocument.Close
Next fileCntr
End If
MsgBox "Macro execution complete."

End With
End Sub

Do change the font color to a color of your choice. You can also modify the path specified in the macro according to your requirement.

Note: I'm assuming that you want to change the font color of only word documents.

Purnima.

 

[DougStroud]

Purnima,
I have not been successful in executing the macro you wrote yet. I found an error on my part, I missed that I needed to store the file in the directory My Documents.
I move the document there and then re-ran the macro. What I see or don't see is that the macro return the message "Macro execution complete."
But I don't see any files being opened at all. I tried this on another file to make sure there was no file corruption.
To test it on my part, can you give me just the code to where the file opens and then stops. I was thinking I would test it to the most basic level. If I get the file to open then I will be working in the right direction.

A question popped into my head after I saw you wrote a piece to open all the files in the My Documents directory, of, does this open the files in only My Documents, or all of the files in this directory and the directories that reside in My Documents too? (I was going to test this macro, but did not want to run the risk of it opening all the files on my computer and turning the text blue).

Thanks,

Doug

 

[purnima]

Doug,

I really don't know why the code doesn't seem to work at your end when it's working perfect at mine.
This macro will open word files in My Documents only. But it will not touch any word files within any other folders under My Documents.

Try this code:

Sub find_replace_all_files()

Dim i As Integer
Dim term As String, fTerm As String, rTerm As String

term = InputBox("Please specify the term to be found and its replacement, separated by a forward slash(/)." _
& vbCrLf & "Example:" & vbCrLf & "Quiksilver/Quicksilver!")

fTerm = Left(term, InStr(term, "/") - 1)
rTerm = Right(term, InStr(term, "/"))

With Application.FileSearch
.FileName = "*.doc"
.LookIn = "C:\My Documents"
.Execute
If .FoundFiles.count <> 0 Then
For i = 1 To .FoundFiles.count
Documents.Open (.FoundFiles(i))
ActiveDocument.TrackRevisions = True
ActiveDocument.Select
With Selection.Find
.Text = fTerm
.Replacement.Text = rTerm
.MatchWildcards = False
.Format=False
End With
Selection.Find.Execute Replace:=wdReplaceAll
ActiveDocument.Save
Next i
End If
MsgBox "Macro execution complete."

End With

End Sub

It's no different from the earlier code, only I have deleted the Activedocument.Close statement. So now, all the files that are being opened by the macro will remain open.

I just don't know what's going wrong at your end. Can you send me a few docs from your My Documents folder, provided they don't contain any inportant and sensitive data? Also, please paste here the exact code you have at your end. I'll test it here on my comp and try to figure out the issue.

Purnima.