Hi,
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()
Wend
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
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()
Wend
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