Suddenly loop through files doesn't work

[fred3]

I've been using this code to update a sequence of files that are listed in column C starting at cell C2. Suddenly, it runs only the first one and stops.

Here is the calling code:

Sub Updater()
'
' Updater Macro
'

'
    Range("C2").Select
   
    Dim cell        As Range
    Dim sFile       As String

    Application.ScreenUpdating = False

    On Error Resume Next
    For Each cell In Range("C2", Cells(Rows.Count, "C").End(xlUp)).Cells
       
        sFile = cell.Value
        If Len(Trim(sFile)) Then
            If Len(Dir(sFile)) Then
            Application.ScreenUpdating = False

                Workbooks.Open sFile
                If Err.Number Then
                    MsgBox "Can't open " & sFile
                    Err.Clear
                Else
                    ActiveWorkbook.Close SaveChanges:=True
                End If
            Else
                MsgBox "File " & sFile & " does not exist"
            End If
        End If
    Next cell

    Application.ScreenUpdating = True
End Sub

The one change that I suspect may have something to do with this is in the called file:

TimeSet = ThisWorkbook.Worksheets("Model").Range("F1").Value
    If Hour(Now) < TimeSet Then
        ThisWorkbook.Save
        ThisWorkbook.Saved = True
        Application.DisplayAlerts = False
        ActiveWorkbook.Close False
        Exit Sub
    End If

where I changed
from:

Application.Quit

to:

ActiveWorkbook.Close False

And, the "from" code used to work!
So I'm chasing dynamic behaviors.....!

 

[Luke M]

When you say it stops, does that mean code errors/breaks, or runs but only opens a single file?
When you debug the code (step through using F8), does it loop correctly?
Is the sheet with file names the active sheet?

I notice that your whole code has a "On Error Resume Next". This is generally considered bad practice, as we can't tell where an error occurred, if any! Try running this, let us know what happens.

Sub Updater()
'' Updater Macro'
'   Range("C2").Select
   
   Dim cell        As Range
   Dim sFile      AsString

    Application.ScreenUpdating = False

  MsgBox "Cells to check: " & Range("C2", Cells(Rows.Count, "C").End(xlUp)).Cells.Count
   For Each cell In Range("C2", Cells(Rows.Count, "C").End(xlUp)).Cells
       
        sFile = cell.Value
       If Len(Trim(sFile)) Then
           If Len(Dir(sFile)) Then
            Application.ScreenUpdating = False
                 On Error Resume Next
                Workbooks.Open sFile
                On Error GoTo 0
               If Err.Number Then
                    MsgBox "Can't open " & sFile
                    Err.Clear
               Else
                    ActiveWorkbook.Close SaveChanges:=True
               EndIf
           Else
                MsgBox "File " & sFile & " does not exist"
           EndIf
       EndIf
   Next cell

    Application.ScreenUpdating = True
EndSub

 

[fred3]

The way it was that it would cycle through all the files in the list of the calling workbook.
Now, it runs one file and stops with the calling workbook remaining open as intended.
If I step through it again, it doesn't increment the called filename - it just opens the first one again.

The called workbook is supposed to be the ActiveWorkbook when the .Close is run.

I'm running it now with your suggested code and it appears to be working when there is no run of the Solver/Optimization in the called workbook. But, when there's a run of the Solver, instead of going back to the For Each, the execution stops and the next step would be running the Updater from the beginning - thus going back to the first filename cell.
So, it's not returning as expected. The optimizer / Solver optional code in the called workbook(s) at the top level is:

Private Sub Workbook_Open()
'2014_06_02 Version
Dim curWorkbook As Workbook
Set curWorkbook = ActiveWorkbook
MyPath = ActiveWorkbook.Path
 
justOpen = ThisWorkbook.Worksheets("Model").Range("G1").Value
'If justOpen=0 then skip all the updates and optimizations and leave the file open.
'If justOpen=1 then update prices and follow the optimization and leave open or close settings.
If justOpen = 1 Then
  Call GetData
 
  Optimize = ThisWorkbook.Worksheets("Model").Range("E1").Value
  If Optimize = 2 Then
  Call Optimize_Big(curWorkbook)
  End If
  If Optimize = 1 Then
  Call Optimize_Outcome(curWorkbook)
  End If
  If Optimize = 0 Then
  End If 'i.e. Do Nothing re: optimization
  
  TimeSet = ThisWorkbook.Worksheets("Model").Range("F1").Value
  If Hour(Now) < TimeSet Then
  ThisWorkbook.Save
  ThisWorkbook.Saved = True
  Application.DisplayAlerts = False
  ActiveWorkbook.Close False
  Exit Sub
  End If
End If

Sheets("Model").Select
  Range("A1").Select
End Sub

 

[fred3]

I ran into this advice:
..it's best to avoid relying on some specific sheet being active at any given time and instead always refer to it directly (by name or by codeName).
Maybe that's what I need to do more in that this app seems to keep changing how it works without much reason that I can determine.
I just need to learn how to do it....

 

[Luke M]

Hi fred3
That would be sound advice. In your Workbook_Open code, I'd change references from using Active to things like:

Set curBook = ThisWorkbook 'Always refers to the workbook where code resides
Set mySheet = curBook.Worksheets("Sheet1")

Hope that helps, and good luck!

 

[fred3]

Thank you!
I'll give that a try.
It appears it's running now even though it appeared not earlier.. ???
I'm thinking now that things like "Active" could cause "system context" things to change around and the code to change what it does - to go from working to not working and vice versa. It would be nice to know how "system context" is changed / controlled. But maybe this coding solution is better than trying to learn all that....