vba遍历工作簿的所有工作表和名称管理器输出及单元格名称

Sub GetSheetConfigWithFilter()
    Dim ws As Worksheet
    Dim nm As Name
    Dim sheetName As String
    Dim nmName As String
    Dim cellValue As Variant
    Dim outputText As String
    Dim filePath As String

    ' 初始化字符串变量
    outputText = ""

    ' 设置文件路径（根据需要修改）
    filePath = "E:\output.txt"

    ' 遍历所有的工作表
    For Each ws In ThisWorkbook.Sheets
        sheetName = ws.Name

        ' 遍历当前工作表的所有名称管理器
        For Each nm In ws.Names
            If nm Is Nothing Then
                a = 1
            Else
                ' 获取名称管理器的名称
                nmName = nm.Name

                ' 移除sheet名，并检查名称是否以 "Mark_" 开头
                Dim sheetDelimiter As String
                sheetDelimiter = IIf(InStr(1, nmName, "'") > 0, "'", "")
                Dim nameWithoutSheet As String
                nameWithoutSheet = Mid(nmName, InStr(nmName, sheetDelimiter) + Len(sheetDelimiter) + Len(sheetName))

                ' 添加 try
                On Error Resume Next

                If Left(nameWithoutSheet, 6) = "!Mark_" Then
                    ' 使用 Evaluate 获取引用范围的值
                    cellValue = ws.Evaluate(nmName)

                    ' 将结果添加到字符串变量中
                    outputText = outputText & "list.Add(new SheetConfig(""" & sheetName & """,""" & nmName & """,""" & CStr(cellValue) & """));" & vbCrLf
                End If

                ' 恢复错误处理
                On Error GoTo 0
            End If
        Next nm
    Next ws

    ' 将字符串变量的内容写入文件
    Open filePath For Output As #1
    Print #1, outputText
    Close #1
End Sub