在VB操作Excel时，如何遍历工作簿中的工作表？

vnking 2013-05-10 12:49:16

在VB操作Excel时，如何遍历工作簿中的工作表，并将每张工作表的名字显示出来？





        Set xlAppFilter = CreateObject("Excel.Application") '创建EXCEL应用类

        Set xlBookFilter = xlAppFilter.Workbooks.Open(strFileNameForFilter)

        xlBookFilter.Activate

        Dim i, Count As Integer

        Count = xlBookFilter.Sheets.Count

    

        For i = 1 To Count

‘以下这句是错的，应该怎样写才是对的呢？

            xlSheetsFilter = xlBookFilter.Sheets.Select(i)

            msgbox xlSheetsFilter.Name

            r = r + 1

            

        Next

        

        xlBookFilter.Close (True)

        Set xlSheetFilter = Nothing

        Set xlBookFilter = Nothing

        xlAppFilter.Quit '关闭EXCEL应用类

        Set xlAppFilter = Nothing '释放EXCEL应用类

...全文

794 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

vnking 2013-05-10

打赏
举报

嗯，我重新测试一下原来的代码，好像加上一个 Set 就好了，难道这个 Set 不能省掉吗？谢谢楼上诸位！

dsd999 2013-05-10

打赏
举报

xlSheetsFilter = xlBookFilter.Sheets(i) 应该是 Set xlSheetsFilter = xlBookFilter.Sheets(i)

vnking 2013-05-10

打赏
举报

我改成这样，好像就行了，仅供参考，共同分享。


        Set xlAppFilter = CreateObject("Excel.Application") '创建EXCEL应用类
        Set xlBookFilter = xlAppFilter.Workbooks.Open(strFileNameForFilter)
        xlBookFilter.Activate
        
        For Each xlSheetFilter In xlBookFilter.Sheets
            xlSheetAddup.Cells(r, 1) = xlSheetFilter.Name
            r = r + 1
        Next

vnking 2013-05-10

打赏
举报

实时错误 '438': 对象不支持该属性或方法我将我的程序全部显示出来，上面只是一部分，功能主要是创建一个新的Excel文件，搜索一个文件夹下的某类文件名的Excel文件，然后将每个工作表的名称写入新的Excel文件中，代码如下：



    Dim xlApp As Excel.Application
    Dim xlBookAddup As Excel.Workbook
    Dim xlSheetAddup As Excel.Worksheet
    
    Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
    Set xlBookAddup = xlApp.Workbooks.Add '创建统计EXCEL工作簿
    Set xlSheetAddup = xlBookAddup.Sheets("Sheet3")
    xlSheetAddup.Delete '删除几张默认页
    Set xlSheetAddup = xlBookAddup.Sheets("Sheet2")
    xlSheetAddup.Delete '删除几张默认页
    Set xlSheetAddup = xlBookAddup.Sheets("Sheet1")
    xlSheetAddup.Activate
    xlSheetAddup.Name = "测试表"
         
    Dim strFileNameForFilter As String
    Dim strDir, strSearchPath As String
    Dim r As Integer
    r = 1
    
    strSearchPath = "c:\temp\多张表"
    strDir = Dir(strSearchPath & "*.xls", vbNormal + vbHidden + vbReadOnly + vbSystem)
    Do While strDir <> ""
        strFileNameForFilter = "c:\temp\" & strDir
   
        Dim xlAppFilter As Excel.Application
        Dim xlBookFilter As Excel.Workbook
        Dim xlSheetFilter As Excel.Worksheet
        
        Set xlAppFilter = CreateObject("Excel.Application") '创建EXCEL应用类
        Set xlBookFilter = xlAppFilter.Workbooks.Open(strFileNameForFilter)
        xlBookFilter.Activate
        Dim i, Count As Integer
        Count = xlBookFilter.Sheets.Count
    
        For i = 1 To Count
            xlSheetsFilter = xlBookFilter.Sheets(i)
            xlSheetAddup.Cells(r, 1) = xlSheetsFilter.Name
            r = r + 1
            
        Next
        
        xlBookFilter.Close (True)
        Set xlSheetFilter = Nothing
        Set xlBookFilter = Nothing
        xlAppFilter.Quit '关闭EXCEL应用类
        Set xlAppFilter = Nothing '释放EXCEL应用类
        
        strDir = Dir
    Loop

    Set xlSheetAddup = Nothing
    xlBookAddup.SaveAs ("c:\temp\测试汇总表.xls") '保存文件
    xlBookAddup.Close (True) '关闭EXCEL工作簿
    xlApp.Quit '关闭EXCEL应用类
    Set xlApp = Nothing '释放EXCEL应用类

dsd999 2013-05-10