Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-14-2012, 12:22 AM
Ajay Shahane Ajay Shahane is offline Find, Replace Windows XP Find, Replace Office 2003
Novice
Find, Replace
 
Join Date: May 2012
Posts: 3
Ajay Shahane is on a distinguished road
Default Find, Replace

Hello everyone,

I am new around here.

Is it possible to have MS Word 2003 find and replace text between a certain text? For instance, my document has many lines with text like:

"Warehouse: city 1, Store: Area 1, Address: Avenue 1, PIPE: 15412."
"Warehouse: city 3, Store: Area 5, Address: Avenue 5, IT: 15412."


"Warehouse: city 8, Store: Area 2, Address: Street 4, GATE: 15412."

Notice that first word (Warehouse) and the last text (15412) remain identical in all the lines.

I want to replace everything between 'Warehouse' and '15412' in every line so the end result may look like:

"Warehouse: Village 31, Store: Zone DC, Address: Part 1, CIR: 15412."
"Warehouse: Town 4, Chain: Zone D2C, email: Blvd 5, CIK: 15412."
"Warehouse: Locality A, Grocer: County 7E, snailmail: Building 4, DIMD: 15412."


Any idea if I can do it?

There are many files and each has about 20 such replacements to be made.

I've a feeling I'm not making enough sense

thank you for reading this post and thank you in advance for any help.

A Shahane
Reply With Quote
  #2  
Old 05-14-2012, 12:39 AM
macropod's Avatar
macropod macropod is online now Find, Replace Windows 7 64bit Find, Replace Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Ajay,

Given that you want different replacement strings in different scenarios, a single Find/Replace isn't going to do it all - you'll need one for each scenario. For each scenario, a simple Find/Replace will do the job, where:
Find = city 1, Store: Area 1, Address: Avenue 1, PIPE
Replace = Village 31, Store: Zone DC, Address: Part 1, CIR
etc. It doesn't seem to me that the 'Warehouse:' or ': 15412' is especially relevant in this context but, if they are, simply include them in the Find/Replace strings also.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 05-15-2012, 03:11 AM
Ajay Shahane Ajay Shahane is offline Find, Replace Windows XP Find, Replace Office 2003
Novice
Find, Replace
 
Join Date: May 2012
Posts: 3
Ajay Shahane is on a distinguished road
Default thank

thank you Paul. That's a pity nothing can be done, since I have quite a few documents wherein to replace this.
That means I'm meant to slog!
A Shahane
Reply With Quote
  #4  
Old 05-15-2012, 03:39 AM
macropod's Avatar
macropod macropod is online now Find, Replace Windows 7 64bit Find, Replace Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Well, no, it just means you need to take a different approach to what you may have had in mind.

Given that you want to process multiple documents, try the following macro. Basically, all you need to do is to create an Excel workbook with the Find & Replace strings in columns A & B, respectively, then run the macro and point it to the folder containing the files to be processed. All documents in that folder will be searched and updated. You will need to supply your own workbook name for the 'StrWkBkNm' variable, your own worksheet name for the 'StrWkSht' variable and, perhaps, some or all of the path also.
Code:
Sub UpdateDocuments()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, strDocNm As String, wdDoc As Document
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String
Dim iDataRow As Long, xlFList As String, xlRList As String, i As Long
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\FindReplaceList.xlsx"
StrWkSht = "Sheet1": strDocNm = ActiveDocument.FullName
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
On Error Resume Next
'Start Excel
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
  MsgBox "Can't start Excel.", vbExclamation
  Exit Sub
End If
On Error GoTo 0
With xlApp
  'Hide our Excel session
  .Visible = False
  ' The file is available, so open it.
  Set xlWkBk = .Workbooks.Open(StrWkBkNm, False, True)
  If xlWkBk Is Nothing Then
    MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
    .Quit: Set xlApp = Nothing: Exit Sub
  End If
  ' Process the workbook.
  With xlWkBk
    'Ensure the worksheet exists
    If SheetExists(xlWkBk, StrWkSht) = True Then
      With .Worksheets(StrWkSht)
        ' Find the last-used row in column A.
        iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp
        ' Capture the F/R data.
        For i = 1 To iDataRow
          ' Skip over empty fields to preserve the underlying cell contents.
          If Trim(.Range("A" & i)) <> vbNullString Then
            xlFList = xlFList & "|" & Trim(.Range("A" & i))
            xlRList = xlRList & "|" & Trim(.Range("B" & i))
          End If
        Next
      End With
    Else
      MsgBox "Cannot find the designated worksheet: " & StrWkSht, vbExclamation
    End If
  .Close False
  End With
  .Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
'Exit if there are no data
If xlFList = "" Then Exit Sub
'Get the folder to process
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
'Process each document in the folder
While strFile <> ""
  If strFolder & "\" & strFile <> strDocNm Then
    Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    'Process each word from the F/R List
    With wdDoc
      With .Range.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .MatchWholeWord = True
        .MatchCase = True
        .Wrap = wdFindContinue
        For i = 1 To UBound(Split(xlFList, "|"))
          .Text = Split(xlFList, "|")(i)
          .Replacement.Text = Split(xlRList, "|")(i)
          .Execute Replace:=wdReplaceAll
        Next
      End With
      'Close the document
      .Close SaveChanges:=True
    End With
  End If
  'Get the next document
  strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub
 
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function
 
Function SheetExists(xlWkBk As Object, SheetName As String) As Boolean
Dim i As Long: SheetExists = False
With xlWkBk
  For i = 1 To .Sheets.Count
    If .Sheets(i).Name = SheetName Then
      SheetExists = True:   Exit For
    End If
  Next
End With
End Function
To see how to install & run the macro, go to: Installing Macros
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-16-2012, 04:07 AM
Ajay Shahane Ajay Shahane is offline Find, Replace Windows XP Find, Replace Office 2003
Novice
Find, Replace
 
Join Date: May 2012
Posts: 3
Ajay Shahane is on a distinguished road
Default

OMG! Paul, thank you for having taken so much pain and time to help me...

The 'all-thumbs' person that I am, it will take me some time to figure out what you have done... thank you again...
A Shahane
Reply With Quote
  #6  
Old 05-19-2018, 04:45 AM
ballpoint ballpoint is offline Find, Replace Windows 10 Find, Replace Office 2016
Advanced Beginner
 
Join Date: Sep 2017
Posts: 42
ballpoint is on a distinguished road
Default

Hi! I am upping this thread because I was wondering if it is possible to use the very same macro to do the same kind of find / replace, but in excel, rather than word files. Am I wrong in assuming that I would just have to amend the ' With wdDoc' string?

Thanks!
Reply With Quote
  #7  
Old 05-19-2018, 04:52 AM
macropod's Avatar
macropod macropod is online now Find, Replace Windows 7 64bit Find, Replace Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by ballpoint View Post
Am I wrong in assuming that I would just have to amend the ' With wdDoc' string?
Very much so - the code would require a major re-write.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 05-19-2018, 04:54 AM
ballpoint ballpoint is offline Find, Replace Windows 10 Find, Replace Office 2016
Advanced Beginner
 
Join Date: Sep 2017
Posts: 42
ballpoint is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Very much so - the code would require a major re-write.
Just when I thought I understood it! Oh, well, I'll just paste my excel column in a word file and process that. That should work too!

Thank you!
Reply With Quote
  #9  
Old 03-10-2023, 11:01 AM
gr8mechguru gr8mechguru is offline Find, Replace Windows 10 Find, Replace Office 2016
Novice
 
Join Date: Mar 2023
Posts: 1
gr8mechguru is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Well, no, it just means you need to take a different approach to what you may have had in mind.

Given that you want to process multiple documents, try the following macro. Basically, all you need to do is to create an Excel workbook with the Find & Replace strings in columns A & B, respectively, then run the macro and point it to the folder containing the files to be processed. All documents in that folder will be searched and updated. You will need to supply your own workbook name for the 'StrWkBkNm' variable, your own worksheet name for the 'StrWkSht' variable and, perhaps, some or all of the path also.
Code:
Sub UpdateDocuments()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, strDocNm As String, wdDoc As Document
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String
Dim iDataRow As Long, xlFList As String, xlRList As String, i As Long
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\FindReplaceList.xlsx"
StrWkSht = "Sheet1": strDocNm = ActiveDocument.FullName
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
On Error Resume Next
'Start Excel
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
  MsgBox "Can't start Excel.", vbExclamation
  Exit Sub
End If
On Error GoTo 0
With xlApp
  'Hide our Excel session
  .Visible = False
  ' The file is available, so open it.
  Set xlWkBk = .Workbooks.Open(StrWkBkNm, False, True)
  If xlWkBk Is Nothing Then
    MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
    .Quit: Set xlApp = Nothing: Exit Sub
  End If
  ' Process the workbook.
  With xlWkBk
    'Ensure the worksheet exists
    If SheetExists(xlWkBk, StrWkSht) = True Then
      With .Worksheets(StrWkSht)
        ' Find the last-used row in column A.
        iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp
        ' Capture the F/R data.
        For i = 1 To iDataRow
          ' Skip over empty fields to preserve the underlying cell contents.
          If Trim(.Range("A" & i)) <> vbNullString Then
            xlFList = xlFList & "|" & Trim(.Range("A" & i))
            xlRList = xlRList & "|" & Trim(.Range("B" & i))
          End If
        Next
      End With
    Else
      MsgBox "Cannot find the designated worksheet: " & StrWkSht, vbExclamation
    End If
  .Close False
  End With
  .Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
'Exit if there are no data
If xlFList = "" Then Exit Sub
'Get the folder to process
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
'Process each document in the folder
While strFile <> ""
  If strFolder & "\" & strFile <> strDocNm Then
    Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    'Process each word from the F/R List
    With wdDoc
      With .Range.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .MatchWholeWord = True
        .MatchCase = True
        .Wrap = wdFindContinue
        For i = 1 To UBound(Split(xlFList, "|"))
          .Text = Split(xlFList, "|")(i)
          .Replacement.Text = Split(xlRList, "|")(i)
          .Execute Replace:=wdReplaceAll
        Next
      End With
      'Close the document
      .Close SaveChanges:=True
    End With
  End If
  'Get the next document
  strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub
 
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function
 
Function SheetExists(xlWkBk As Object, SheetName As String) As Boolean
Dim i As Long: SheetExists = False
With xlWkBk
  For i = 1 To .Sheets.Count
    If .Sheets(i).Name = SheetName Then
      SheetExists = True:   Exit For
    End If
  Next
End With
End Function
To see how to install & run the macro, go to: Installing Macros


I am using Excel 2016. On using above code, (strDocNm = ActiveDocument.FullName) it is showing as run time error 4248. for time being i have bypassed (strDocNm = ActiveDocument.FullName)

Further please help me to extend the above code for
1) Header and Footer sections also
2) highlighting the replaced text in Main Body, Header and Footer
3) code for inserting Tables and paragraphs at Bookmarks, looping through all or few word files (of same folder) through Same Excel File
Reply With Quote
Reply

Tags
find replace

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find, Replace Bad view when using Find and Find & Replace - Word places found string on top line paulkaye Word 4 12-06-2011 11:05 PM
Find, Replace Is there a way to use "find/replace" to find italics words? slayda Word 3 09-14-2011 02:16 PM
Find, Replace Help with find and replace or query and replace shabbaranks Excel 4 03-19-2011 08:38 AM
find and replace anil3b2 Word 0 08-03-2010 11:30 PM
Find /Replace Help ilcaa Word 2 11-02-2009 08:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:21 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft