readall gives error for last file from getfolder().files

[Brent]

Cannot figure out why this happens only on the lsat file found by the
getfolders().files. Any help would be great!!

Set apps = fso.getfolder("c:\ds_wind").Files
Do
Set apps = fso.getfolder("c:\ds_wind").Files
Loop While apps.Count < (Range("d1") - Date + 3)
For Each Filename In apps
Set fs = fso.opentextfile(Filename)
txtstring = ""
txtstring = fs.readall
fs.close
txtstring = Right(txtstring, Len(txtstring) - InStr(txtstring, "R:") - 18)
txtstring = Left(txtstring, InStr(txtstring, "TOTAL:") - 1)
txtarray = Split(txtstring, " ")
txtarray2 = ""
For i = 0 To UBound(txtarray)
txtarray(i) = Trim(txtarray(i))
txtarray(i) = Replace(txtarray(i), Chr(10), "", 1, -1,
vbBinaryCompare)
txtarray(i) = Replace(txtarray(i), Chr(13), "", 1, -1,
vbBinaryCompare)
If txtarray(i) <> "" And txtarray(i) <> "." Then
txtarray2 = txtarray2 + "," + txtarray(i)
End If
Next
txtarray = Split(txtarray2, ",")
filedate = "" & txtarray(1) & ""
i = 9
If Range("a5").Value = "" Then
rwidx = 5
Else
rwidx = Range("a4").End(xlDown).Row + 1
End If
While i < UBound(txtarray)
Cells(rwidx, 1) = "'" & filedate
Cells(rwidx, 2) = "'" & txtarray(i)
Cells(rwidx, 3) = "'" & txtarray(i + 5)
Cells(rwidx, 4) = "'" & txtarray(i + 6)
Cells(rwidx, 5) = "'" & txtarray(i + 9)
i = i + 10
rwidx = rwidx + 1
Wend
Next

 

[Joel]

I don't like the way you are getting the list of fileanmes and then counting
the number of files. You are making assumptions that all the files are in
the directory when you use the fiollowing instructions


Do
Set apps = fso.getfolder("c:\ds_wind").Files
Loop While apps.Count < (Range("d1") - Date + 3)

Try this code. I'm not sure what files you are looking for so this line
may need to be modified
If FileDate >= Range("d1") Then


First = True
Do
If First = True Then
FName = Dir("c:\ds_wind\*.*")
First = False
Else
FName = Dir()
End If
If FName <> "" Then
Set f = fso.GetFile(FName)
FileDate = f.DateLastModified
If FileDate >= Range("d1") Then
Set fs = fso.opentextfile(FName)
txtstring = ""
txtstring = fs.readall
fs.Close
txtstring = Right(txtstring, Len(txtstring) - InStr(txtstring,
"R:") - 18)
txtstring = Left(txtstring, InStr(txtstring, "TOTAL:") - 1)
txtarray = Split(txtstring, " ")
txtarray2 = ""
For i = 0 To UBound(txtarray)
txtarray(i) = Trim(txtarray(i))
txtarray(i) = Replace(txtarray(i), Chr(10), "", _
1, -1, vbBinaryCompare)
txtarray(i) = Replace(txtarray(i), Chr(13), "", _
1, -1, vbBinaryCompare)
If txtarray(i) <> "" And txtarray(i) <> "." Then
txtarray2 = txtarray2 + "," + txtarray(i)
End If
Next
txtarray = Split(txtarray2, ",")
FileDate = "" & txtarray(1) & ""
i = 9
If Range("a5").Value = "" Then
rwidx = 5
Else
rwidx = Range("a4").End(xlDown).Row + 1
End If
While i < UBound(txtarray)
Cells(rwidx, 1) = "'" & FileDate
Cells(rwidx, 2) = "'" & txtarray(i)
Cells(rwidx, 3) = "'" & txtarray(i + 5)
Cells(rwidx, 4) = "'" & txtarray(i + 6)
Cells(rwidx, 5) = "'" & txtarray(i + 9)
i = i + 10
rwidx = rwidx + 1
Wend
End If
End If
Loop While FName <> ""

 

[Brent]

The only reason I am counting the numbers is because I am downloading the
files from an ftp site into a local folder and vba does not wait for the
files to complete downloading before searching the folder for files. Thus,
the search will "find" the file although it is not yet downloaded and gives
errors because it cannot find the file not yet downloaded. Does this make
sense? Is there a way to hold the execution until all files have been
downloaded? The problem is that when using the shell comand to ftp, I think
you can only hold the program for the shell command and not the cmd.exe that
executes as a result of the call from the shell command. Any clues?? Thank
you. Also, other things are running on the computer, as well as other
cmd.exes.