Excel文件的VBA代码中的文本替换

首先

对不起花一些时间发布,但我正在为它创建一个UI,以便它不仅帮助你,而且帮助任何其他人寻找相同的功能.

您需要首先启用 Trust Access to the VBA project Object model

打开Excel并点击File Tab | Options | Trust Center | Trust Center Settings | Macro Settings

启用宏并单击Trust access to Visual Basic projects

下一个在VBA编辑器

单击工具| Options并在“Editor” Tab下选择复选框Require Variable Declaration

下一个从 下载 Sample 文件 ,并简单地按下 Run Button 在 Sheet1 中启动用户表单,如下所示。

简单地选择只有Excel文件的文件夹.输入相关信息并单击Start Replace并完成:)

使用的代码

Sheet1码区

 Option Explicit

Private Sub CommandButton1_Click()
    UserForm1.Show
End Sub
 

userform代码区域

 Option Explicit

Private Sub CommandButton1_Click()
    Dim Ret
    Ret = BrowseForFolder
    If Ret = False Then Exit Sub
    TextBox1.Text = Ret
End Sub

Private Sub CommandButton3_Click()
    On Error GoTo Whoa

    Dim wb As Workbook
    Dim strPath As String, strfile As String
    Dim strToReplaceWith As String, strToReplace As String
    Dim i As Long, j As Long

    Dim VBE As Object

    strPath = TextBox1.Text & "\"

    strfile = Dir(strPath)

    While strfile <> ""
        Set wb = Workbooks.Open(strPath & strfile)

        Set VBE = ActiveWorkbook.VBProject

        If VBE.VBComponents.Item(1).Properties("HasPassword").Value = False Then
            If VBE.VBComponents.Count > 0 Then
                For i = 1 To VBE.VBComponents.Count
                    VBE.VBComponents.Item(i).Activate

                    If VBE.VBE.CodePanes.Item(i).CodeModule.CountOfLines > 0 Then
                        For j = 1 To VBE.VBE.CodePanes.Item(i).CodeModule.CountOfLines
                            If InStr(1, VBE.VBE.CodePanes.Item(i).CodeModule.Lines(j, 1), TextBox2.Text, vbTextCompare) Then
                                strToReplace = VBE.VBE.CodePanes.Item(i).CodeModule.Lines(j, 1)
                                strToReplaceWith = Replace(strToReplace, TextBox2.Text, TextBox3.Text, 1, 1, vbTextCompare)
                                VBE.VBE.CodePanes.Item(i).CodeModule.ReplaceLine j, strToReplaceWith
                            End If
                        Next
                    End If
                Next i
            End If
        End If

        wb.Close True

        strfile = Dir
    Wend

LetsContinue:
    Application.ScreenUpdating = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

'~~> Function to pop the browse folder dialog
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
    Dim ShellApp As Object

    '~~> Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

    '~~> Set the folder to that selected.  (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0

    '~~> Destroy the Shell Application
    Set ShellApp = Nothing

    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select

    Exit Function

Invalid:
    BrowseForFolder = False
End Function

Private Sub CommandButton4_Click()
    Unload Me
End Sub
 

更多SNAPSHOTS

在宏运行之前需要替换Whose代码的文件

运行宏后

编辑

替代风险简介

如果上述 wikisend 链接死亡,文件可以从 here 下载。