excel - VBA - 循环文件夹 - 替换文件名中的子字符串

Question

挑战:
循环浏览文件夹以更新文件名。但是，有重复的文件名，用括号区分。
当我执行附加的代码时，它会更新整个文件名而不保留括号元素，从而导致编译错误。
目标
使用“表格”选项卡上的映射，遍历文件夹并使用新代码更新文件名；偏移量(0,1)。
例子:
旧 - 874031 (1).jpg
新 - 100 (1).jpg


我已经搜索了该网站，但我无法达到预期的结果，非常感谢任何帮助。

Sub Find_Replace_FileName()

Dim strFolder As String
    
strFolder = "C:\Users\jason\"
    
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Table")

Dim x As Long, LastRow As Long
Dim file As Variant
Dim objFSO As Object: Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objFolder As Object: Set objFolder = objFSO.GetFolder(strFolder)
Dim filename As String

    With ws
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).row
        
        For Each file In objFolder.Files
        filename = Left(file.Name, InStr(file.Name, ".") - 1)
            For x = 2 To LastRow

                'Using this method I want to substitue the_
                'filename found with corresponding value
                
                If InStr(filename, .Cells(x, 1).Value) Then
                    
                    file.Name = Replace(filename, filename, .Cells(x, 1).Offset(0, 1).Value) & ".jpg"
                End If
    
            Next x
        Next file
    End With

End Sub

编辑 21/02/2022
感谢您对此的回答，它们非常适合最初发布的内容!
我正在尝试进行压力测试，但无法确定以下情况的解决方案。
场景
如果在另一个文件名的子字符串中找到 col A 的内容怎么办？例子:
文件夹内容

table

这可以正常工作，但是如果我将 87403156 放在下面的单元格 874031 中，我的循环将错误地更新 87403156 的文件名。我理解它为什么这样做，但无法确定或找到类似的问题。

Answer 1

Option Explicit
Sub Find_Replace_FileName1()
        
    Const strFolder = "C:\Users\jason\"
          
    Dim objFSO As Object: Set objFSO = CreateObject("Scripting.FileSystemObject")
    Dim objFolder As Object: Set objFolder = objFSO.GetFolder(strFolder)
    
    Dim ws As Worksheet, ar, file As Object
    Dim LastRow As Long, i As Long, n As Long, s As String
    
    Set ws = ActiveWorkbook.Sheets("Table")
    With ws
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        ar = .Range("A2:B" & LastRow)
        For Each file In objFolder.Files
            s = file.Name
            For i = 1 To UBound(ar)
                If InStr(s, ar(i, 1)) Then
                   file.Name = Replace(s, ar(i, 1), ar(i, 2))
                   n = n + 1
                   'Debug.Print n, s, file.Name
                   Exit For
                End If
            Next
        Next
    End With
    MsgBox n & " files renamed", vbInformation

End Sub