一种基于vba的多次正则替换

程序的目的是减少手工使用正则替换次数，达到自动多次替换的目的。

数据源如下表：

中文	英文
是>	Yes>
否>	No>
...	...
...	...

提示：

源程序源文件为dita文件，涉及文件权限问题不再展示，使用之前需要更改替换文件格式。

Sub 正则批处理()
Dim ReadStream As Object
Dim WriteStream As Object
Dim reg As Object
'文件路径
mypath = ThisWorkbook.Path & "\"
MyFile = mypath & Dir(mypath & "*.dita")
Do While MyFile <> mypath
'读取文件
Set ReadStream = CreateObject("ADODB.Stream")
    With ReadStream
            .Charset = "UTF-8"
            .Open
            .LoadFromFile MyFile
            strbuf = .readtext
            .Flush
            .Close
    End With
Set ReadStream = Nothing
'正则匹配
Set reg = CreateObject("VBScript.Regexp")
i = 2
Do
With reg
    .Global = 1
    .Pattern = Cells(i, "A")
    strbuf = .Replace(strbuf, Cells(i, "B").Value)
End With
i = i + 1
Loop Until Cells(i, "a") = ""
'回写所取内容
Set WriteStream = CreateObject("ADODB.Stream")
With WriteStream
            .Charset = "UTF-8"
            .Open
            .WriteText strbuf
            .SaveToFile MyFile, 2
            .Flush
            .Close
    End With
Set WriteStream = Nothing
MyFile = mypath & Dir
Loop
End Sub