☰

尝试在 Excel 表中查找并替换以空格结尾的匹配字符串

tag-icon • tag-icon microsoft-excel microsoft-word vba
尝试在 Excel 表中查找并替换以空格结尾的匹配字符串

基本上，我有 VBA 代码，可以使用 excel 表中 A 列的内容搜索 word 文档，并将其替换为 B 列中的内容。这些是包含字母和数字的字符串，例如，查找 CE-1 并将其替换为 R-1000。但是，每当我运行它时，它还会匹配 CE-13 之类的实例，需要将其替换为其他内容。基本上，有没有办法调整我的代码以查找和替换实例（当且仅当它们以空格结尾）。

我尝试在 Excel 的 A 列所有单元格末尾添加一个空格，然后在 VBA 中使用 .IgnoreSpace=False，但无济于事。

ub Replace_ID()
'
' Replace_ID Macro
'
Application.ScreenUpdating = True
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String
Dim bStrt As Boolean, iDataRow As Long, bFound As Boolean
Dim xlFList As String, xlRList As String, i As Long, Rslt
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Desktop\Request ID mapping.xlsx"
StrWkSht = "Sheet2"
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
' Test whether Excel is already running.
On Error Resume Next
bStrt = False ' Flag to record if we start Excel, so we can close it later.
Set xlApp = GetObject(, "Excel.Application")
'Start Excel if it isn't running
If xlApp Is Nothing Then
  Set xlApp = CreateObject("Excel.Application")
  If xlApp Is Nothing Then
    MsgBox "Can't start Excel.", vbExclamation
    Exit Sub
  End If
  ' Record that we've started Excel.
  bStrt = True
End If
On Error GoTo 0
'Check if the workbook is open.
bFound = False
With xlApp
  'Hide our Excel session
  If bStrt = True Then .Visible = False
  For Each xlWkBk In .Workbooks
    If xlWkBk.FullName = StrWkBkNm Then ' It's open
      Set xlWkBk = xlWkBk
      bFound = True
      Exit For
    End If
  Next
  ' If not open by the current user.
  If bFound = False Then
  ' Check if another user has it open.
  If IsFileLocked(StrWkBkNm) = True Then
  ' Report and exit if true
  MsgBox "The Excel workbook is in use." & vbCr & "Please try again later.", vbExclamation, "File in use"
  If bStrt = True Then .Quit
    Exit Sub
  End If
  ' The file is available, so open it.
  Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm)
  If xlWkBk Is Nothing Then
    MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
    If bStrt = True Then .Quit
    Exit Sub
  End If
End If
' Process the workbook.
With xlWkBk.Worksheets(StrWkSht)
  ' Find the last-used row in column A.
  ' Add 1 to get the next row for data-entry.
  iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp
  ' Output the captured 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
If bFound = False Then xlWkBk.Close False
If bStrt = True Then .Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
'Process each word from the F/R List
For i = 1 To UBound(Split(xlFList, "|"))
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .IgnoreSpace = False
    .MatchWholeWord = True
    .MatchCase = True
    .Wrap = wdFindStop
    .Text = Split(xlFList, "|")(i)
    .Execute
    'To automatically change the found text:
    '? comment-out/delete the previous line and the Do While Loop
    '? uncomment the next two lines
    '.Replacement.Text = Split(xlRList, "|")(i)
    '.Execute Replace:=wdReplaceAll
  End With
  'Ask the user whether to change the found text
  Do While .Find.Found
    .Duplicate.Select
    Rslt = MsgBox("Replace this instance of:" & vbCr & _
      Split(xlFList, "|")(i) & vbCr & "with:" & vbCr & _
      Split(xlRList, "|")(i), vbYesNoCancel)
    If Rslt = vbCancel Then Exit Sub
    If Rslt = vbYes Then .Text = Split(xlRList, "|")(i)
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
Next
Application.ScreenUpdating = True
End Sub
'
Function IsFileLocked(strFileName As String) As Boolean
On Error Resume Next
Open strFileName For Binary Access Read Write Lock Read Write As #1
Close #1
IsFileLocked = Err.Number
Err.Clear
End Function

答案1

要在搜索文本和替换文本中包含添加的空格，您需要在两行代码中将空格连接到这些字符串上。

首先，修改此代码：

.Text = Split(xlFList, "|")(i)
.Execute

到

.Text = Split(xlFList, "|")(i) & " "
.Execute

然后，更改此代码：

If Rslt = vbYes Then .Text = Split(xlRList, "|")(i)
.Collapse wdCollapseEnd
.Find.Execute

到：

If Rslt = vbYes Then .Text = Split(xlRList, "|")(i) & " "
.Collapse wdCollapseEnd
.Find.Execute

这应该能满足您的要求。如果您仍然无法正确匹配要替换的文本（例如，如果这些值中的任何一个出现在句子末尾，后面跟着标点符号，或者出现在没有尾随空格的行末），您可能需要考虑使用正则表达式搜索。

相关内容