VBS遍历Excel工作表的实现代码

1. 基本遍历（按工作表顺序）

' 创建Excel对象
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True ' 设置为True可见，False不可见

' 打开工作簿
Set objWorkbook = objExcel.Workbooks.Open("C:\路径\文件名.xlsx")

' 遍历所有工作表
For Each objWorksheet In objWorkbook.Worksheets
    WScript.Echo "工作表名称: " & objWorksheet.Name
    WScript.Echo "索引号: " & objWorksheet.Index
    WScript.Echo "可见性: " & objWorksheet.Visible
    WScript.Echo "---------------------------------"
Next

' 关闭并退出
objWorkbook.Close
objExcel.Quit

' 释放对象
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

2. 遍历并获取工作表详细信息

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False

Set objWorkbook = objExcel.Workbooks.Open("C:\测试\数据.xlsx")

Dim wsCount
wsCount = objWorkbook.Worksheets.Count
WScript.Echo "总工作表数: " & wsCount

' 按索引遍历
For i = 1 To wsCount
    Set objSheet = objWorkbook.Worksheets(i)

    WScript.Echo "========== 工作表信息 =========="
    WScript.Echo "索引: " & i
    WScript.Echo "名称: " & objSheet.Name
    WScript.Echo "类型: " & TypeName(objSheet)

    ' 获取使用范围
    If Not IsEmpty(objSheet.UsedRange) Then
        WScript.Echo "使用范围: " & objSheet.UsedRange.Address
        WScript.Echo "行数: " & objSheet.UsedRange.Rows.Count
        WScript.Echo "列数: " & objSheet.UsedRange.Columns.Count
    Else
        WScript.Echo "工作表为空"
    End If

    WScript.Echo ""
Next

objWorkbook.Close False ' False表示不保存更改
objExcel.Quit

3. 遍历并处理每个工作表中的数据

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open("C:\数据\报表.xlsx")

' 遍历所有工作表
For Each objSheet In objWorkbook.Worksheets
    WScript.Echo "正在处理工作表: " & objSheet.Name

    ' 获取使用范围
    Set objRange = objSheet.UsedRange

    ' 遍历单元格数据
    For i = 1 To objRange.Rows.Count
        For j = 1 To objRange.Columns.Count
            Dim cellValue
            cellValue = objRange.Cells(i, j).Value

            ' 这里可以添加数据处理逻辑
            If Not IsEmpty(cellValue) Then
                ' 示例：处理非空单元格
                If InStr(1, cellValue, "重要", vbTextCompare) > 0 Then
                    WScript.Echo "找到重要数据: " & cellValue & _
                                " (位置: " & objSheet.Name & "!" & _
                                objRange.Cells(i, j).Address & ")"
                End If
            End If
        Next
    Next

    WScript.Echo "工作表 " & objSheet.Name & " 处理完成"
    WScript.Echo "---------------------------------"
Next

objWorkbook.Save ' 保存更改
objWorkbook.Close
objExcel.Quit

4. 过滤特定类型的工作表

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open("C:\文件\工作簿.xlsx")

' 只处理普通工作表（排除图表工作表等）
For Each objSheet In objWorkbook.Worksheets
    ' 检查工作表类型
    If objSheet.Type = -4167 Then ' xlWorksheet = -4167
        WScript.Echo "普通工作表: " & objSheet.Name

        ' 这里可以添加针对普通工作表的处理代码
        ' 例如：复制数据、修改格式等

    ElseIf objSheet.Type = -4109 Then ' xlChart = -4109
        WScript.Echo "图表工作表: " & objSheet.Name
        ' 跳过或特殊处理图表工作表
    End If
Next

objWorkbook.Close False
objExcel.Quit

5. 遍历并导出工作表名称到文本文件

Dim fso, outputFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set outputFile = fso.CreateTextFile("C:\输出\工作表列表.txt", True)

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open("C:\源文件\数据.xlsx")

outputFile.WriteLine "工作簿: " & objWorkbook.Name
outputFile.WriteLine "生成时间: " & Now()
outputFile.WriteLine "工作表列表:"
outputFile.WriteLine String(50, "=")

Dim counter
counter = 0

' 遍历并写入文件
For Each objSheet In objWorkbook.Worksheets
    counter = counter + 1
    outputFile.WriteLine counter & ". " & objSheet.Name
Next

outputFile.WriteLine String(50, "=")
outputFile.WriteLine "总计: " & counter & " 个工作表"

outputFile.Close
objWorkbook.Close False
objExcel.Quit

WScript.Echo "工作表列表已导出到: C:\输出\工作表列表.txt"

6. 函数封装版本

' 函数：获取工作簿的所有工作表名称
Function GetAllSheetNames(workbookPath)
    Dim arrSheets(), objExcel, objWorkbook, i
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False

    On Error Resume Next
    Set objWorkbook = objExcel.Workbooks.Open(workbookPath)
    If Err.Number <> 0 Then
        GetAllSheetNames = Array()
        Exit Function
    End If

    ReDim arrSheets(objWorkbook.Worksheets.Count - 1)

    For i = 1 To objWorkbook.Worksheets.Count
        arrSheets(i - 1) = objWorkbook.Worksheets(i).Name
    Next

    objWorkbook.Close False
    objExcel.Quit

    GetAllSheetNames = arrSheets
End Function

' 使用示例
Dim sheetNames, sheetName
sheetNames = GetAllSheetNames("C:\文档\测试.xlsx")

If UBound(sheetNames) >= 0 Then
    WScript.Echo "工作表列表:"
    For Each sheetName In sheetNames
        WScript.Echo "- " & sheetName
    Next
Else
    WScript.Echo "无法打开工作簿或工作簿为空"
End If

使用注意事项：

快速测试脚本：

' 创建一个新工作簿并测试遍历
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add

' 添加一些工作表
objWorkbook.Worksheets.Add
objWorkbook.Worksheets.Add

' 重命名工作表
objWorkbook.Worksheets(1).Name = "数据表"
objWorkbook.Worksheets(2).Name = "汇总表"
objWorkbook.Worksheets(3).Name = "图表"

' 遍历显示
WScript.Echo "工作簿中的工作表:"
For i = 1 To objWorkbook.Worksheets.Count
    WScript.Echo i & ": " & objWorkbook.Worksheets(i).Name
Next

' 不保存直接关闭
objWorkbook.Close False
objExcel.Quit

选择适合你需求的代码片段，根据需要修改文件路径和处理逻辑。