时间戳

首页 > 解决方案 > 尝试重命名工作表时星号不起作用

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

问题描述

我正在尝试整理几个工作簿，这些工作簿都有一个从“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_Analysis\7-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

有人可以帮忙吗？

标签： excelvbarenameworksheet

解决方案

根据问题修订进行编辑。

添加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_Analysis\7-Pass_EVAP"  
     Workbooks.Open (FolderPath & "\Combined_7.xlsx")

     ' you could just use this
     Workbooks.Open ("C:\Data_Analysis\7-Pass_EVAP\Combined_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

推荐阅读