Why am I getting: For Loop Not Initialised 2

[Gavona]

With wbkResults.Worksheets("Sheet1")
    r = .Range("A65536").End(xlUp).Row + 1
        On Error Resume Next
    For Each FileItem In SourceFolder.Files
        Call StatusMonitor(FileItem.Path)
        ' display file properties
        .Cells(r, 1).Formula = FileItem.Path ' & FileItem.Name
        .Cells(r, 2).Formula = FileItem.Size
        .Cells(r, 3).Formula = FileItem.Type
        .Cells(r, 4).Formula = FileItem.DateCreated
        .Cells(r, 5).Formula = FileItem.DateLastAccessed
        .Cells(r, 6).Formula = FileItem.DateLastModified
        .Cells(r, 7).Formula = FileItem.Attributes
        .Cells(r, 8).Formula = FileItem.ShortPath ' 8 character filename
'       .Cells(r, 9).Formula = FileItem.Path
        On Error Resume Next
        .Cells(r, 10).Formula = FileItem.Name
        On Error GoTo 0
        .Cells(r, 9).FormulaR1C1 = "=SUBSTITUTE(RC[-8],RC[1],"""",1)"
        .Cells(r, 9).Value = .Cells(r, 9).Value

    On Error GoTo 0
        r = r + 1 ' next row number
    [red]Next FileItem[/red]
End With

The code debugs at the red row above with the error message "for loop not initialised"
Now at this point r = 9850 so the loop was ok the previous 9849 times!

Just to say this error was obtained by a co-worker. When I ran the last week all was fine.

We are trying to document file locations prior to a massive re-structuring of folders etc. The other issue is that the macro is apparently finding more files than explorer reports if you right-click a folder and ask for properties.

Gavin

 

[PHV]

What are SourceFolder and StatusMonitor ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

also, you might want to change this to be more generalized...

With wbkResults.Worksheets("Sheet1")
    r = [b].Cells(.Cells.Rows.Count, 1)[/b].End(xlUp).Row + 1
'.....

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Status monitor:

Application.StatusBar = Right(Format(Time, "short time") & " " & StatusText & "        " & Format(Round((Timer - Start) / 60, 2), "short time") & "  minutes so far", 255)

No loops etc.

Sourcefolder: Probably easiest to give you the rest of the code! In doing this I see that the code is recursive...that's surely the issue. Now I obtained the original code from somewhere else and don't really understand recursion but I ran this code successfully last week on the same set of folders. Could it be a memory issue?

Sub [b]ListFilesInFolder[/b](SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    [red]Set SourceFolder = FSO.GetFolder(SourceFolderName)[/red]

Call StatusMonitor("Starting")
[green]With wbkResults.Worksheets("Sheet1")

#####code as previously posted######

End With[/green]
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            [b]ListFilesInFolder[/b] SubFolder.Path, True
        Next SubFolder
    End If
    Columns("A:j").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

Gavin

 

[Gavona]

I am guessing a memory issue - maybe my co-worker has more "stuff" open than when I ran the routine. Certainly she is probably running the VBA code multiple times. Is there anything I should be doing to 'properly' release the stack memory at the end of the routine?

But would that explain the specific error message?

Gavin

 

[strongm]

You may want to start by looking carefully at your error handling

 

[PHV]

You need a single FileSystemObject instance, eg:

Dim FSO As Scripting.FileSystemObject
Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
If FSO Is Nothing Then
    Set FSO = New Scripting.FileSystemObject
End If
Set SourceFolder = FSO.GetFolder(SourceFolderName)
Call StatusMonitor("Starting")
With wbkResults.Worksheets("Sheet1")

#####code as previously posted######

End With
If IncludeSubfolders Then
    For Each SubFolder In SourceFolder.SubFolders
        ListFilesInFolder SubFolder.Path, True
    Next SubFolder
End If
Columns("A:j").AutoFit
Set FileItem = Nothing
Set SourceFolder = Nothing
ActiveWorkbook.Saved = True
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gavona]

Skip: thanks for the suggestion. Though it will be a long time before we get beyond 2003 I'll start the more resilient practice now.

Strongm: Thanks, yes I see a rogue On Error Resume next - not convinced it causes any issues but once I am back at work I will test what happens if I remove it.
The second use was because we found a file whose name started with an = sign.

PHV:I see - star for that!! Wonder if that will resolve the issue. On Friday I ran the code on the same folder and got much further than my co=worker. Presumably I should actually leave
Set FSO = Nothing
at the end of my code? Surely can't hurt?



Gavin

 

[PHV]

Presumably I should actually leave Set FSO = Nothing at the end of my code?
Don't set FSO to nothing as the code is recursive ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gavona]

Your advice seems to have resolved things - for me at least. My co-worker has not yet tested it.
Thanks again.

BTW

Presumably I should actually leave Set FSO = Nothing at the end of my code?
Don't set FSO to nothing as the code is recursive ...

That bit of code is outside the recursive bit.

Gavin

 

[strongm]

No, it isn't - or at least no it isn't in the ListFilesInFolder code you posted earlier.

 

[Gavona]

Hmmm........Am I not understanding? This is where I meant, right at the end of the code. By my reckoning it would only be executed once.

    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

Gavin

 

[jges]

The subroutine calls itself, so everything between Sub... and End Sub gets executed on each recursive call.

But since FSO gets instantiated at the beginning of the sub, I don't think it will break your code if you set it to nothing at the end of the sub. However, you might see some performance improvement if you create the FSO outside the sub so you don't have to create/destroy the object each time through the subroutine.

 

[Gruuuu]

Perhaps you could include some logic to check to see if the procedure is complete before freeing up your objects.

 

[Gavona]

Of course Gruuu - good idea that will show me what is happening.

Gavin