Dynamic change of limits in for...next loop

[gw.boswell]

I have a for...next loop that performs certain actions of a list of
files. These file names are the same except for a numeric identifier
(i.e testfile1.txt, testfile2.txt, testfile4.txt). Occasionally the
file numeric identifiers are not contiguous (see example). I have put
an error trap to deal with the missing file. However, since the
number of files is known (and fixed) when I skip a file number that
does not exist, that means one iteration of the for...next loop has
happened and therefore one less file of the total will be processed.
So if there are 50 files and iterations = 50, then if the file numbers
go to, say, 60, then the files numbered 51-60 will not be processed.

I tried the following code to increment the for...next limits
dynamically but it doesn't work. The limits remain the same. This is
true if I try to adjust either the lower or upper limit. Any ideas?

With Application.FileSearch
.LookIn = FullName
.SearchSubFolders = False
.FileName = "*.txt"
.MatchTextExactly = False
.Execute
Iterations = .FoundFiles.Count
End With

For Index = 1 To Iterations

On Error Resume Next
Documents.Open FileName:=FullName & ShortName & Index & ".txt",
Visible:=True
If Err.Number <> 0 Then
Iterations = Iterations + 1
GoTo ReturnIt
End If

...do stuff to opened file

ReturnIt:

Next Index

 

[meatshield]

Foundfiles returns an object that you can loop through with the file
path, so you can reference that in your For -each-next loop
For Index = 1 To Iterations

On Error Resume Next
Documents.Open FileName:=application.filesearch.foundfiles(index)
Visible:=True
If Err.Number <> 0 Then
'Iterations = Iterations + 1 'this is unnecessary
GoTo ReturnIt
End If

 

[Tim Williams]

dim x as integer
dim iMissing as integer
dim sName as string

iMissing=0
x=1
do while iMissing<10

sName=FullName & ShortName & i & ".txt"

if Dir(sName)<>"" then
Documents.Open FileName:=sname, Visible:=True
'do stuff with document
iMissing=0
else
iMissing=iMissing+1
end if

i=i+1
loop


This will keep trying filenames until 10 consecutive names are not found. Increase this limit if you need.

 

[gw.boswell]

Foundfiles returns an object that you can loop through with the file
path, so you can reference that in your For -each-next loop
For Index = 1 To Iterations

On Error Resume Next
Documents.Open FileName:=application.filesearch.foundfiles(index)
Visible:=True
If Err.Number <> 0 Then
'Iterations = Iterations + 1 'this is unnecessary
GoTo ReturnIt
End If







- Show quoted text -

Excellent. That worked like a charm. Thanks a bunch for the help.

Garry