Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
readall gives error for last file from getfolder().files
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
readall gives error for last file from getfolder().files
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
readall gives error for last file from getfolder().files
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. "Joel" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File error when opening some Excel files | Excel Discussion (Misc queries) | |||
EXCEL FILES ARE NOT OPENING ERROR IS this file is not in recogniz | Excel Discussion (Misc queries) | |||
how to default WritePassword under excel workbook property for any files as long as those files are the offsprings of the parent file | Excel Programming | |||
Trying to open Excel/Word files error message "Unable to read file | Excel Discussion (Misc queries) | |||
Need a "GetFolder method" | Excel Programming |