VBA 修改表名

功能介绍：修改文件下所有工作簿中第一个表单的名 为 工作簿的名字

Sub 替换表名()

Dim folderPath As String

Dim fileName As String


Application.ScreenUpdating = False

Application.DisplayAlerts = False

 folderPath = SelectFolder
 
         If (folderPath = "") Then
                Exit Sub
        End If
   
  fileName = Dir(folderPath)
  
 Do While fileName <> ""
 
    Set Workbook = Workbooks.Open(folderPath & fileName)
    
    If IsValidSheetName(Workbook.Name) Then

    Workbook.Sheets(1).Name = Workbook.Name
    
    Else

     MsgBox Workbook.Name & " 不是有效的工作表名称。"
    End If
    
    

    Workbook.Close SaveChanges:=True
    
    fileName = Dir
    
Loop

Application.ScreenUpdating = True

Application.DisplayAlerts = True

  MsgBox "文件名称替换完成。"


End Sub


Function SelectFolder()

Dim folderPath As String

With Application.FileDialog(msoFileDialogFolderPicker)
   .InitialFileName = ThisWorkbook.Path
   .Title = "选择文件夹"
   If .Show = True Then
       folderPath = .SelectedItems(1) & "\"
    End If
    End With
 SelectFolder = folderPath

End Function


Function IsValidSheetName(sheetName As String) As Boolean
    On Error GoTo ErrorHandler ' 开始错误处理
    
    ' 尝试将工作表重命名为提供的名称
    ThisWorkbook.Sheets(1).Name = sheetName
    
    ' 如果没有错误，则名称有效
    IsValidSheetName = True
    Exit Function
    
ErrorHandler:
    ' 如果发生错误，则名称无效
    IsValidSheetName = False
End Function