asked on Add a folder picker to a search and replace macro
Dear Experts:
below macro does search and replace operations in nested subfolders.
The 'master folder' is hard coded on line 6. I wonder whether the macro can be tweaked to accommodate the following two requirements.
1. A folder picker is to let the user choose the 'master folder'
2. The built in Search and Replace Dialog is to appear where the user can enter his/her search and replace criteria.
Help is much appreciated.
Thank you very much in advance.
Regards, Andreas
I wonder whether somebody could
below macro does search and replace operations in nested subfolders.
The 'master folder' is hard coded on line 6. I wonder whether the macro can be tweaked to accommodate the following two requirements.
1. A folder picker is to let the user choose the 'master folder'
2. The built in Search and Replace Dialog is to appear where the user can enter his/her search and replace criteria.
Help is much appreciated.
Thank you very much in advance.
Regards, Andreas
I wonder whether somebody could
Sub x_SearchAndReplace_MultipleFiles_Folders_Subfolders()
Dim FSO As Object
Dim ROOT As Object
Dim fldr As Object
Const strFolder = "C:\Trial\"
Set FSO = CreateObject("scripting.filesystemobject")
If Not FSO.folderexists(strFolder) Then
MsgBox "Folder '" & strFolder & "' not found - Exiting routine", , "Error"
Exit Sub
End If
Set ROOT = FSO.getfolder(strFolder & "\")
processFolder ROOT.path
For Each fldr In ROOT.subfolders
processFolder fldr.path & "\"
Next
End Sub
Sub processFolder(strFolder As String)
Dim strFile As String
Dim doc As Document
Dim rng As Word.Range
Dim fileSet As Object
strFile = Dir$(strFolder & "*.docx")
Do Until strFile = ""
Set doc = Documents.Open(strFolder & strFile)
For Each rng In doc.StoryRanges
With rng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "15.500"
.Replacement.Text = "19.500"
.Replacement.Font.Size = 9
.Forward = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
Next rng
doc.Save
doc.Close
strFile = Dir$()
Loop
End Sub
Microsoft WordVB Script
Last Comment
Andreas Hermle
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Rgonzo,
the folder picker works just fine, the search and replace dialog comes up but whatever I enter into the search and replace fields nothing gets updated. I am not quite sure whether this dialog field is working as part of a macro in the first place, since I am not able to confirm any settings with an OK button. I just am able to close it.
If I tweak your code and comment out line 23 till 30 and change line 54 and 55 accordingly, everything works fine.
Regards, Andreas
the folder picker works just fine, the search and replace dialog comes up but whatever I enter into the search and replace fields nothing gets updated. I am not quite sure whether this dialog field is working as part of a macro in the first place, since I am not able to confirm any settings with an OK button. I just am able to close it.
If I tweak your code and comment out line 23 till 30 and change line 54 and 55 accordingly, everything works fine.
Regards, Andreas
to use the built-in replace you have to first either click Replace or replace all then Close
if you remember the normal behavior of the replace Dialog is to stay open as long as it isn't closed
Regards
if you remember the normal behavior of the replace Dialog is to stay open as long as it isn't closed
Regards
Andreas... Just confirming... Your folder contains .docx files, as shown in first post, line 26?
ASKER
Dear both,
sorry for the delay in getting back to you.
Rgonzo: okay, will try it out and then let you know
NVIT: yes they are all docx-files
sorry for the delay in getting back to you.
Rgonzo: okay, will try it out and then let you know
NVIT: yes they are all docx-files
ASKER
Dear both,
having heeded your comments, the things I said before, I regret, are still valid, i.e.
@ NVIT:
looks awesome. It is a pity that line 100-105 do not work on my machine, ie.
... I get the error message: 424 runtime error. The following line gets highlighted:
If UCase( Right( WScript.FullName, 12 ) ) = "\CSCRIPT.EXE" (line 100)
I have to comment out all these lines, i.e. 100-105 for the macro to come up with the InputBoxes
The Input Boxes appear as desired, the strange thing is that whatever I enter as search and replace text, nothing is replaced. The files get opened and saved superfast, but no replacement action whatsoever.
@Rgonzo: I heeded your advice, i.e. I clicked replace / replace all, and then closed the dialog field.
But then, the macro loops thru all the files, open and closes them, but no replacement action whatsoever is completed. Strange isn't it. Anyhow the Folder Picker works great and this is the most important part for me.
Regards, Andreas
having heeded your comments, the things I said before, I regret, are still valid, i.e.
@ NVIT:
looks awesome. It is a pity that line 100-105 do not work on my machine, ie.
... I get the error message: 424 runtime error. The following line gets highlighted:
If UCase( Right( WScript.FullName, 12 ) ) = "\CSCRIPT.EXE" (line 100)
I have to comment out all these lines, i.e. 100-105 for the macro to come up with the InputBoxes
The Input Boxes appear as desired, the strange thing is that whatever I enter as search and replace text, nothing is replaced. The files get opened and saved superfast, but no replacement action whatsoever.
@Rgonzo: I heeded your advice, i.e. I clicked replace / replace all, and then closed the dialog field.
But then, the macro loops thru all the files, open and closes them, but no replacement action whatsoever is completed. Strange isn't it. Anyhow the Folder Picker works great and this is the most important part for me.
Regards, Andreas
ASKER
Dear both,
I suggest splitting the points as I did. I tweaked both codes a little bit so now they work just fine.
Thank you very much for your great and professional help. I really appreciate it.
Regards, Andreas
I suggest splitting the points as I did. I tweaked both codes a little bit so now they work just fine.
Thank you very much for your great and professional help. I really appreciate it.
Regards, Andreas
thank you very much for your great and professional support.
Dear NVIT:
looks awesome. It is a pity that line 100-105 does not work on my machine, but I am sure this has something to do that I run the macro on my business computer at my company.
Open in new window
I have to comment out all these lines, i.e. 100-105 for the macro to come up with the InputBoxes
The Input Boxes appear as desired, the strange thing is that whatever I enter as search and replace text, nothing is replaced. The files get opened and saved superfast, but no replacement action whatsoever. Any idea why?
Thank you and Regards, Andreas