点击宏按钮生成新工作表时需自定义工作表名称

问题描述 #

现有用于工作数据管理自动化追踪器的Arrange宏，每次运行会生成两个新工作表，但工作表名称为默认的SheetXX序号格式，删除后重新运行序号会递增，需要改为指定的自定义名称。

修改方案 #

核心思路是在添加工作表后立即设置自定义名称，同时处理重名情况（避免运行宏时因工作表已存在报错）。以下是修改后的完整代码：

Sub Arrange() 
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Sheets("Consolidated Sheet").Select
    ActiveWorkbook.Queries.Add Name:="Invoked Function", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = KMIP_Cleanup(Query2)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
    ActiveWorkbook.Queries.Add Name:="Arranged Links", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Arranged_Data(#""Invoked Function"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"

    ' 处理第一个自定义名称工作表（示例名称：清理后数据）
    On Error Resume Next
    Set ws1 = ThisWorkbook.Worksheets("清理后数据")
    On Error GoTo 0
    If Not ws1 Is Nothing Then
        ws1.Delete ' 若已存在同名表则删除
    End If
    Set ws1 = ThisWorkbook.Worksheets.Add
    ws1.Name = "清理后数据" ' 设置自定义名称

    With ws1.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Invoked Function"";Extended Properties=""""" _
        , Destination:=ws1.Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Invoked Function]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Invoked_Function"
        .refresh BackgroundQuery:=False
    End With

    ' 处理第二个自定义名称工作表（示例名称：整理后链接）
    On Error Resume Next
    Set ws2 = ThisWorkbook.Worksheets("整理后链接")
    On Error GoTo 0
    If Not ws2 Is Nothing Then
        ws2.Delete ' 若已存在同名表则删除
    End If
    Set ws2 = ThisWorkbook.Worksheets.Add
    ws2.Name = "整理后链接" ' 设置自定义名称

    With ws2.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Arranged Links"";Extended Properties=""""" _
        , Destination:=ws2.Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Arranged Links]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Arranged_Links"
        .refresh BackgroundQuery:=False        
    End With

    Sheets("Macros").Select
    Application.CommandBars("Queries and Connections").Visible = False
End Sub

关键修改点 #

• 引入工作表对象变量（ws1、ws2），替代依赖ActiveSheet的写法，提升代码稳定性
• 添加重名检查逻辑：如果目标名称的工作表已存在，先删除旧表再创建新表（可根据需求调整为提示或跳过）
• 明确设置新工作表的自定义名称，替换默认的SheetXX格式

注意事项 #

• 自定义名称不能包含Excel禁止的字符：/ : * ? " < > |
• 确保名称在工作簿中唯一，避免冲突

内容的提问来源于stack exchange，提问作者Chitralekh Barve