Avatar of Andreas Hermle
Andreas Hermle
Flag for Germany 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

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

Open in new window

Microsoft WordVB Script

Avatar of undefined
Last Comment
Andreas Hermle

8/22/2022 - Mon
SOLUTION
NVIT

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Rgonzo1971

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Andreas Hermle

ASKER
Dear both,

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.
If UCase( Right( WScript.FullName, 12 ) ) = "\CSCRIPT.EXE" Then
   ' If so, use StdIn and StdOut
   WScript.StdOut.Write myPrompt & " "
   UserInput = WScript.StdIn.ReadLine
Else

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
Andreas Hermle

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
Rgonzo1971

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
NVIT

Andreas... Just confirming... Your folder contains .docx files, as shown in first post, line 26?
Andreas Hermle

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
Andreas Hermle

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Andreas Hermle

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