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

本文链接：https://blog.csdn.net/nbspzs/article/details/135369404

该博客主要围绕VBA展开，介绍了利用VBA遍历工作簿的所有工作表，还涉及名称管理器的输出以及单元格名称相关内容，聚焦于信息技术中VBA在处理工作簿方面的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

Sub ExportNameManagers()
    Dim nm As Name
    Dim ws As Worksheet
    Dim outputPath As String
    Dim fileNum As Integer
    Dim outputLine As String
    
    ' Set output file path (modify as needed)
    outputPath = ThisWorkbook.Path & "\NameManagers.txt"
    
    ' Get active worksheet
    Set ws = ActiveSheet
    
    ' Open text file for writing
    fileNum = FreeFile
    Open outputPath For Output As #fileNum
    
    ' Loop through all names in the workbook
    For Each nm In ThisWorkbook.Names
        ' Check if name starts with "Mark_" and refers to the active sheet
        If nm.Name Like "Mark_*" And InStr(nm.RefersTo, ws.Name & "!") > 0 Then
            outputLine = "list.Add(new NameManage(""" & ws.Name & """, """ & nm.Name & """, """ & nm.RefersTo & """));"
            Print #fileNum, outputLine
        End If
    Next nm
    
    ' Close the file
    Close #fileNum
    
    MsgBox "Name Managers exported to " & outputPath, vbInformation
End Sub