excel - 如何在另一个excel文档中查找和替换

Question

我正在尝试创建一个宏(在 Excel 中)以打开另一个 Excel 工作表，执行查找替换，然后保存它。

我已经用这样的word文档实现了这一点:

...
Dim wrdDoc As Word.Document
If wrdApp Is Nothing Then Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Open(TemplateFilePath)
...

Call WordReplace(wrdDoc,"a","b")

....

Private Sub WordReplace(wrdDoc As Word.Document, sFind As String, sReplace As String)
  With wrdDoc.Content.Find
    .Text = sFind
    .Replacement.Text = sReplace
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll
  End With
End Sub

我粗略地尝试将上述代码中的“Word”一词替换为“Excel”，但无济于事。

有人可以指出我正确的方向吗？

Answer 1

你可以这样做:

Sub a()

Dim excDoc As Workbook
....

Set excDoc = Workbooks.Open("c:\mata.xls")
Call WordReplace(excDoc, "a", "b")
....
End Sub

Private Sub WordReplace(excDoc As Variant, sFind As String, sReplace As String)

Dim sht As Worksheet

For Each sht In excDoc.Worksheets
  With sht
       .Cells.Replace What:=sFind, Replacement:=sReplace, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
  End With
Next
End Sub