#1
|
|||
|
|||
Find and replace
Hi,
Am having code for bulk find and replace macro and is it possibe to insert a code for char more than 255 strings, if any one knows help me and am basic knowledge on this and copied from forums and your help will be appreciated and thanks a lot... Am attaching sample text string and that string need to be replace in a batch process... Code:
Sub BulkFindReplace() 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\nkhaja\Desktop\errr\" & "Replacement1.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 'Set highlight color. Options.DefaultHighlightColorIndex = wdYellow .ClearFormatting .Replacement.ClearFormatting .Replacement.Font.Name = "Angsana New" .Replacement.Font.Bold = True .Replacement.Font.Color = wdColorBlack .Replacement.Highlight = True 'Set highlight to replace setting. .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 Thanks in advance........ |
#2
|
||||
|
||||
I have already answered your question about strings longer than 255 characters at: RUNTIME ERROR 5174. Thread closed.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do you use the find and replace tool to find dates and times in Excel 2013? | Jules90 | Excel | 3 | 04-14-2020 07:40 PM |
In Find and Replace, can Word stop after each Replace? | wardw | Word | 1 | 06-08-2017 02:47 PM |
Find what box in Find and replace limits the length of a search term | Hoxton118 | Word VBA | 7 | 06-10-2014 05:05 AM |
Bad view when using Find and Find & Replace - Word places found string on top line | paulkaye | Word | 4 | 12-06-2011 11:05 PM |
Help with find and replace or query and replace | shabbaranks | Excel | 4 | 03-19-2011 08:38 AM |