尝试重命名工作表时星号不起作用

Question

我正在尝试对几本工作簿进行排序，这些工作簿都有一个以"2020"开头的主工作表

我想简单地循环浏览每个工作簿并更改名称，但以下代码不起作用：

Sheets("2020-*").Name = "MAIN"

但是，以下代码确实有效：

Sheets("2020-07-01 11.05.04").Name = "MAIN"

这是完整的代码：

Sub dTa_dTb_fix()
'
' dTa_dTb_fix Macro
Dim FolderPath As String, path As String, count As Integer
FolderPath = "C:Data_Analysis7-Pass_EVAP"
path = FolderPath & "2020-*.xlsx"
Filename = Dir(path)
Workbooks.Open (FolderPath & "Combined_7.xlsx")
count = 2
Application.ScreenUpdating = False
Do While Filename <> ""
Set ClosedBook = Workbooks.Open(Filename)
Sheets("2020-*").Name = "MAIN"

Worksheets("MAIN").ListObjects("Table1").ListColumns("dTa").DataBodyRange.FormulaR1C1 = "=ABS(AVERAGE([@[EVAPORATOR PAO OUTLET TEMP  °C]]-[@[EVAPORATOR PAO INLET TEMP  °C]])-[@[CONDENSER PAO INLET TEMP °C ]])"
Worksheets("MAIN").ListObjects("Table1").ListColumns("dTb").DataBodyRange.FormulaR1C1 = "=ABS(AVERAGE([@[EVAPORATOR PAO INLET TEMP  °C]]-[@[EVAPORATOR PAO OUTLET TEMP  °C]])-[@[CONDENSER PAO OUTLET TEMP °C ]])"
ClosedBook.Close SaveChanges:=False

Filename = Dir()
count = count + 1
Loop
Application.ScreenUpdating = True

End Sub

有人能帮忙吗？

Answer 1

根据问题修订进行编辑。

添加了InStr()以在ActiveWorkbook.Worksheets(I).Name的内容中搜索2020-标准。

---

InStr参数

InStr VBA函数接受4个参数，其中2个是可选的：

InStr([Start], MainString, SubString, [Compare As VbCompareMethod])

src:https://wellsr.com/vba/2016/excel/use-vba-instr-to-test-if-string-contains-substring/

---

(未经测试(

Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer
Dim FolderPath As String, path As String, count As Integer

Application.ScreenUpdating = False
' not needed if FolderPath is a constant
FolderPath = "C:Data_Analysis7-Pass_EVAP"  
Workbooks.Open (FolderPath & "Combined_7.xlsx")
' you could just use this
Workbooks.Open ("C:Data_Analysis7-Pass_EVAPCombined_7.xlsx")
WS_Count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_Count
If InStr(1, ActiveWorkbook.Worksheets(I).Name , "2020-") <> 0 Then
ActiveWorksheet.Name = "MAIN"
Worksheets("MAIN").ListObjects("Table1").ListColumns("dTa").DataBodyRange.FormulaR1C1 = "=ABS(AVERAGE([@[EVAPORATOR PAO OUTLET TEMP  °C]]-[@[EVAPORATOR PAO INLET TEMP  °C]])-[@[CONDENSER PAO INLET TEMP °C ]])"
Worksheets("MAIN").ListObjects("Table1").ListColumns("dTb").DataBodyRange.FormulaR1C1 = "=ABS(AVERAGE([@[EVAPORATOR PAO INLET TEMP  °C]]-[@[EVAPORATOR PAO OUTLET TEMP  °C]])-[@[CONDENSER PAO OUTLET TEMP °C ]])"
End If
Next I
ClosedBook.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub