Word macro that can call cells out of Excel for a find and replace

Nov 11, 2022
Reaction score

I am trying to create a macro that can open a file, go to excel and select cell I2, find the values in I2 inside the file, then go back to excel and replace the found values with the values in cell J2. This then needs to be repeated (cells I3, J3, I4, etc.) until the end of the spreadsheet.

This is what I currently have. It is made for finding and replacing a value based on the input box. I want it to go to the cells instead for the value:

Sub FindReplaceMultiFile()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, wdDoc
Dim i As Integer
i = 0
Dim regExp As regExp
Set regExp = New regExp
Dim colMatches As MatchCollection
Dim match As match
strFolder = GetFolder1
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc*", vbNormal)

Dim strFindText, strReplaceText, strRegExpEscape As String

strFindText = InputBox("What's your problem?") 'Put something into the inputbox parameters to give it a title/prompt the user
strReplaceText = InputBox("What are you going to do about it?") 'Same here

regExp.Global = True
regExp.IgnoreCase = False
regExp.MultiLine = False

While strFile <> ""
Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)

Set colMatches = regExp.Execute(wdDoc.Range.Text)

With wdDoc
With .Range.Find

.Text = strFindText
.Replacement.Text = strReplaceText
.Format = True
.MatchWildcards = True
.Forward = True
.MatchCase = True
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
If .Found = False Then Debug.Print "No Match found in " & wdDoc
If .Found = True Then
Debug.Print colMatches.Count & " Match(es) found in " & wdDoc
i = i + 1
End If

End With
.Close SaveChanges:=True
End With
strFile = Dir()
Set wdDoc = Nothing
Application.ScreenUpdating = True

End Sub
Function GetFolder1() As String
Dim oFolder As Object
GetFolder1 = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Pick a thingy", 0)
If (Not oFolder Is Nothing) Then GetFolder1 = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question