时间戳

首页 > 解决方案 > 从特定文件夹子文件夹重命名特定工作表

excel - 从特定文件夹子文件夹重命名特定工作表

问题描述

我在文件夹中有几个 excel 文件，并且只想重命名文件夹中每个文件的特定工作表，其中包含

即。GTLB，薪水，GROC

每个文件都有一张上述字符，其他文件有不同的名称。因此，如果工作表名称包含上述字符，则将其更改为GROCERY。

提前致谢

标签： excelvba

解决方案

尝试使用它，它将遍历文件夹尝试查找文件（excel文件）并尝试在已指定的文件中查找字符串，如果找到匹配项，请更改名称。

  Sub LoopThroughFiles()
 'loops through all files in a folder
Dim MyObj As Object, MySource As Object, file As Variant
Dim wbk As Workbook
Dim path As String
Dim st As String

file = Dir("H:\TestCopy\testing\") 'file name
path = "H:\TestCopy\testing\" 'directory path

While (file <> "")
Set wbk = Workbooks.Open("H:\TestCopy\testing\" & file)
     MsgBox "found " & file
    ' path = path & file 'path and filename
     Call newloopTrhoughBooks
     wbk.Save
     wbk.Close
   ' Call loop_through_all_worksheets(path)
 file = Dir
Wend
End Sub

 Sub newloopTrhoughBooks()
 Dim book As Workbook, sheet As Worksheet, text As String, text1 As String
  Dim logic_string As String
   Dim logic_string2 As String
  Dim logic_string3 As String

   logic_string = "GTLB"
    logic_string2 = "SALARY"
    logic_string3 = "GROC"

   For Each book In Workbooks
  text = text & "Workbook: " & book.Name & vbNewLine & "Worksheets: " &   vbNewLine

  For Each sheet In book.Worksheets
  text = text & sheet.Name & vbNewLine
  text1 = sheet.Name
   If StrComp(logic_string, text1) = 1 Or StrComp(logic_string2, text1) = 1 Or StrComp(logic_string3, text1) = 1 Then 'compare file name
  ActiveSheet.Name = text1
  ActiveSheet.Name = "Change1"
  End If
  Next sheet
 text = text & vbNewLine
 Next book
MsgBox text

 End Sub

推荐阅读