CODEWITHSUNDEEP
vbaexcel
user6189814
user6189814

Reputation: 13

Using for each to loop through a series of workbooks

I am a VBA newbie trying to figure out how to loop through a series of workbooks and their sheets in an effort to find a specific sheet but are having some trouble with my object variables.

Below is the code I have "written" (glued together might be a more apt description). I have tried various corrections but only seem to be moving the problem from one place to another. Any help will be appreciated!

Sub NestedForEach()
'Create an object variable to represent each worksheet
Dim WS As Worksheet
Dim WB As Workbook
Set WB = ActiveWorkbook
Set WS = Workbook.Sheets
'create a boolen variable to hold the status of whether we found worksheet "D"
Dim IsFound As Boolean
'initialise the IsFound boolean variable
IsFound = False

    For Each WB In Application.Workbooks
        For Each WS In WB.Worksheets
            If WS.Name = "d" Then
                IsFound = True
                Exit For
            End If
        Next WS
    Next WB

    If IsFound Then
        MsgBox "sheet D has been found in " & ActiveWorkbook.Name
    Else
        MsgBox "we could not locate sheet D in any of the open workbooks"
    End If


End Sub

Upvotes: 1

Views: 2971

Answers (2)

ASH
ASH

Reputation: 20302

Just 1 week ago I wrote a script to go to a specified folder (the user chooses) and list all Excel files and sheet names in that folder.

Public Sub LoopAllExcelFilesInFolder()

Dim WB As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim sht As Worksheet
Dim LastRow As Long

Application.DisplayAlerts = False

Sheets("ListFilesInFolder").Select
Set sht = ThisWorkbook.Worksheets("ListFilesInFolder")
sht.Activate
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select


  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
myExtension = "*.xl*"

'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)

  Do While myFile <> ""

      Set WB = Workbooks.Open(Filename:=myPath & myFile)

        With Application
            .AskToUpdateLinks = False
        End With

        For Each Sheet In Workbooks(myFile).Worksheets
        LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row + 1
            Workbooks("Questionaire-Mock-Up.xlsb").Worksheets("ListFilesInFolder").Cells(LastRow, 1).Value = myPath & myFile
            Workbooks("Questionaire-Mock-Up.xlsb").Worksheets("ListFilesInFolder").Cells(LastRow, 2).Value = myFile
            Workbooks("Questionaire-Mock-Up.xlsb").Worksheets("ListFilesInFolder").Cells(LastRow, 3).Value = Sheet.Name
                File = InStr(myFile, ".xl") - 1
                LeftName = Left(myFile, File)
            Workbooks("Questionaire-Mock-Up.xlsb").Worksheets("ListFilesInFolder").Cells(LastRow, 4).Value = LeftName
            LastRow = LastRow + 1
        Next Sheet

      Workbooks(myFile).Close SaveChanges:=False
      myFile = Dir
  Loop

ResetSettings:

Application.DisplayAlerts = True

End Sub

Upvotes: 0

Ralph
Ralph

Reputation: 9434

Only few changes were necessary in order to make your code work:

Option Explicit

Sub NestedForEach()
'Create a Worksheet variable to represent one worksheet
Dim WS As Worksheet
Dim WB As Workbook

'create a boolen variable to hold the status of whether we found worksheet "D"
Dim IsFound As Boolean
'initialise the IsFound boolean variable
IsFound = False

    For Each WB In Application.Workbooks
        For Each WS In WB.Worksheets
            If WS.Name = "d" Then
                IsFound = True
                MsgBox "sheet D has been found in " & WB.Name
                Exit Sub
            End If
        Next WS
    Next WB

    MsgBox "we could not locate sheet D in any of the open workbooks" & _
        Chr(10) & "which are open in this instance of Excel" & _
        Chr(10) & "(in case multiple Excels are running)"

End Sub

Let me know if you have any question regarding the changes.

Upvotes: 2

Related Questions