Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    skip worksheet on looping (excel)

    for Each sfl In fld.SubFolders
    wsh.Range("A" & I) = sfl.Path
    strFile = Dir(sfl.Path & "*.xls")
    Set wbk = Workbooks.Open(Filename:=sfl.Path & "" & strFile, AddToMRU:=False)
    wsh.Range("B" & I) = wbk.Worksheets("Job Summary").Range("C5")

    is a piece of loop which extracts data from a work sheet, the work sheet is called "Job summary" if the workbook does not contain a "Job Summary" worksheet would i include a else next statement to skip this enitre work book and move on?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: skip worksheet on looping (excel)

    You'll have to test whether the workbook contains a worksheet named "Job summary". One way to do this is to loop through the worksheets until you find it:

    Dim wsh2 As Worksheet
    For Each wsh2 In wbk.Worksheets
    If wsh2.Name = "Job Summary" Then
    wsh.Range("B" & I) = wsh2.Range("C5")
    ...
    Exit For
    End If
    Next wsh2

    Another way is to suppress errors temporarily:

    Dim wsh2 As Worksheet
    Dim f As Long
    Set wbk = Workbooks.Open(Filename:=sfl.Path & "" & strFile, AddToMRU:=False)
    On Error Resume Next
    Set wsh2 = wbk.Worksheets("Job Summary")
    f = Err
    On Error GoTo ErrHandler
    If f = 0 Then
    wsh.Range("B" & I) = wsh2.Range("C5")
    ...
    End If

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skip worksheet on looping (excel)

    You could do something like this:

    <pre>Dim oSh As Worksheet
    For Each sfl In fld.SubFolders
    wsh.Range("A" & I) = sfl.Path
    strFile = Dir(sfl.Path & "*.xls")
    Set wbk = Workbooks.Open(Filename:=sfl.Path & "" & strFile, AddToMRU:=False)
    Set oSh = Nothing
    On Error Resume Next
    Set oSh = wbk.Worksheets("Job Summary")
    On Error GoTo 0
    If Not oSh Is Nothing Then
    wsh.Range("B" & I) = oSh.Range("C5")
    End If
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skip worksheet on looping (excel)

    Where would i slip this into

    Dim wsh2 As Worksheet
    For Each wsh2 In wbk.Worksheets
    If wsh2.Name = "Job Summary" Then
    wsh.Range("B" & I) = wsh2.Range("C5")
    ...
    Exit For
    End If
    Next wsh2

    if i am pulling multiple cells from the worksheet if there is a Job Summary worksheet?

    Option Explicit


    Sub errhand()
    Dim fso As Object
    Dim fld As Object
    Dim sfl As Object
    Dim strPath As String
    Dim strFile As String
    Dim I As Long, J As Long, JMax As Long
    Dim wbk As Workbook
    Dim wsh As Worksheet

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set wsh = ThisWorkbook.Worksheets(1)
    wsh.Range("A:B:C").Clear
    strPath = wsh.Range("AC1")
    Set fld = fso.GetFolder(strPath)
    I = 1
    For Each sfl In fld.SubFolders
    wsh.Range("A" & I) = sfl.Path
    strFile = Dir(sfl.Path & "*.xls")
    If strFile = "" Then
    wsh.Range("AC" & I) = "No workbook found"
    Else
    Set wbk = Workbooks.Open(Filename:=sfl.Path & "" & strFile, AddToMRU:=False)
    wsh.Range("B" & I) = wbk.Worksheets("Job Summary").Range("C5")
    wsh.Range("C" & I) = wbk.Worksheets("Job Summary").Range("C6")
    wsh.Range("D" & I) = wbk.Worksheets("Job Summary").Range("J8")
    wsh.Range("E" & I) = wbk.Worksheets("Job Summary").Range("J9")
    wsh.Range("F" & I) = wbk.Worksheets("Job Summary").Range("J7")
    wsh.Range("G" & I) = wbk.Worksheets("Job Summary").Range("C29")
    wsh.Range("H" & I) = wbk.Worksheets("Job Summary").Range("C30")
    wsh.Range("J" & I) = wbk.Worksheets("Job Summary").Range("J15")
    wsh.Range("K" & I) = wbk.Worksheets("Job Summary").Range("D26")
    wsh.Range("M" & I) = wbk.Worksheets("Job Summary").Range("D23")
    wsh.Range("N" & I) = wbk.Worksheets("Job Summary").Range("D24")
    JMax = wbk.Worksheets("JobChartsData").Range("F65536").En d(xlUp).Row + 1
    If JMax < 7 Then
    MsgBox "No data in workbook " & strFile & "worksheet JobChartsData"
    Else
    For J = 7 To JMax
    If wbk.Worksheets("JobChartsData").Range("F1").Offset (J, 0).Value > 0 Then
    Range(wbk.Worksheets("JobChartsData").Range("B1"). Offset(J - 3, 0), wbk.Worksheets("JobChartsData").Range("B1").Offset (J - 3, 11)).Copy
    wsh.Paste Destination:=wsh.Range("A1").Offset(I, 15)
    Application.CutCopyMode = False
    Exit For
    End If
    Next J
    If J > JMax Then
    MsgBox "No data in workbook " & strFile & "worksheet JobChartsData"
    End If
    End If
    End If
    I = I + 1
    wbk.Close SaveChanges:=False
    Next sfl
    ExitHandler:
    Set wbk = Nothing
    Set sfl = Nothing
    Set fld = Nothing
    Set fso = Nothing
    ThisWorkbook.Close SaveChanges:=True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: skip worksheet on looping (excel)

    The code I posted is meant to replace

    wsh.Range("B" & I) = wbk.Worksheets("Job Summary").Range("C5")
    wsh.Range("C" & I) = wbk.Worksheets("Job Summary").Range("C6")
    wsh.Range("D" & I) = wbk.Worksheets("Job Summary").Range("J8")
    wsh.Range("E" & I) = wbk.Worksheets("Job Summary").Range("J9")
    wsh.Range("F" & I) = wbk.Worksheets("Job Summary").Range("J7")
    wsh.Range("G" & I) = wbk.Worksheets("Job Summary").Range("C29")
    wsh.Range("H" & I) = wbk.Worksheets("Job Summary").Range("C30")
    wsh.Range("J" & I) = wbk.Worksheets("Job Summary").Range("J15")
    wsh.Range("K" & I) = wbk.Worksheets("Job Summary").Range("D26")
    wsh.Range("M" & I) = wbk.Worksheets("Job Summary").Range("D23")
    wsh.Range("N" & I) = wbk.Worksheets("Job Summary").Range("D24")

    You must fill in the dots ... with lines similar to

    wsh.Range("B" & I) = wsh2.Range("C5")

    for columns C through N.

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skip worksheet on looping (excel)

    Does yours fit in the same way as Hans's would?

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skip worksheet on looping (excel)

    What would cause an automation error?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: skip worksheet on looping (excel)

    All kinds of things. You'll have to be more specific.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skip worksheet on looping (excel)

    Any code that works with the sheet that may be missing would go where this line is located:

    <pre> wsh.Range("B" & I) = oSh.Range("C5")
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •