#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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! |
#7
|
||||
|
||||
Very much so - the code would require a major re-write.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
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! |
#9
|
|||
|
|||
Quote:
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 |
Tags |
find replace |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Bad view when using Find and Find & Replace - Word places found string on top line | paulkaye | Word | 4 | 12-06-2011 11:05 PM |
Is there a way to use "find/replace" to find italics words? | slayda | Word | 3 | 09-14-2011 02:16 PM |
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 |