在每个工作簿vba中遍历工作簿和工作表

I want to loop through all Workbooks in a folder and then loop through all worksheets of each workbook. I have the code below but currently the problem is with the line For Each ws in StrFile.Sheets:

我想遍历文件夹中的所有工作簿,然后遍历每个工作簿的所有工作表。我有下面的代码,但目前的问题是StrFile.Sheets中的For Each ws行:

Sub LoopThroughFiles()
    Dim StrFile As String
    StrFile = Dir("C:\Users\A9900899\Desktop\bob\VBAProject\Raw\")
    Do While Len(StrFile) > 0
        Debug.Print StrFile
        For Each ws In StrFile.Sheets
            Debug.Print ws.Name
        Next ws
        StrFile = Dir
    Loop
End Sub

What is my mistake here?

我的错在哪里?

1 个解决方案

#1

In order to loop through all Workbook.Sheets you need to Set wb to the current StrFile, and then loop through wb.Sheets.

为了遍历所有Workbook.Sheets,你需要将wb设置为当前的StrFile,然后循环遍历wb.Sheets。

Using Application.ScreenUpdating = False and Application.DisplayAlerts = False will minimize all screen flickering and Excel alerts.

使用Application.ScreenUpdating = False和Application.DisplayAlerts = False将最小化所有屏幕闪烁和Excel警报。

Code

Option Explicit

Sub LoopThroughFiles()

    Dim StrFile As Variant
    Dim fPath As String
    Dim wb As Workbook
    Dim ws As Worksheet

    fPath = "C:\Users\A9900899\Desktop\bob\VBAProject\Raw\"
    StrFile = Dir(fPath)

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    While StrFile <> ""
        Debug.Print StrFile
        If StrFile Like "*xls*" Then ' check that current file is Excel type
            Set wb = Workbooks.Open(fPath & StrFile)
            For Each ws In wb.Sheets
                Debug.Print ws.Name
            Next ws
            wb.Close False ' close workbook and don't save changes
        End If
        StrFile = Dir
    Wend
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

#1