Open objFile For Input As iFileNum opens only 512 files and fails to open 513th

[lionne]

hi there,

The task is to extract a special string from .log files in one folder. The number of such .log files is > 4k.
I faced the problem with reading the files. Here's the code snippet:

[vba]
Private Sub cmb_Import_Click()

Dim PathSelected As String
Dim Folder As Object
Dim iFileNum As Long

Dim objFso As Object
Dim objFiles As Object
Dim objFile As Object

Set Folder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder:", 0)
PathSelected = Folder.self.Path

strExt = "log"
iFileNum = FreeFile()

LineNum = 0
Row = 3
'Set Error Handling
'On Error GoTo EarlyExit

'Create objects to get a count of files in the directory
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFiles = objFso.GetFolder(PathSelected).Files

'Count files (that match the extension if provided)
For Each objFile In objFiles
If Right(objFile.Name, 3) = strExt Then 'open only .log Files
'open the file to read lines
Open objFile For Input As iFileNum
'loop through the lines
Do While Not EOF(iFileNum)
'... omitted as not relevant for the problem
Loop
Close iFileNum
iFileNum = iFileNum + 1
End If
Next objFile

EarlyExit:
'Clean up
On Error Resume Next

Set objFiles = Nothing
Set objFso = Nothing
Set objFile = Nothing
On Error GoTo 0

End Sub
[/vba]
The problem is, when iFileNum reaches 513 it crashes with the following error: "Runtime error 52: File name or file number is wrong."

Is it true, that Excel is not able to open the 513th file in the same run?


Any hints are as always highly appreciated..

[Kenneth Hobs]

You can try adding a DoEvents.

I suspect that the files are not being closed and it is a limit of files open in the Temp folder.

I suggest adding Path and maybe encapsulate that in quotes.
[VBA]Open objFile.Path For Input As iFileNum [/VBA]

Lastly, use FreeFile to set your filenumber for Open. e.g.
[VBA]Function IsFileWriteable(StrFilePath As String) As Boolean
Dim FileNum As Integer
IsFileWriteable = False
FileNum = FreeFile
On Error Resume Next
Open StrFilePath For Input Lock Read Write As #FileNum ' Open file and lock it.
If Err.Number = 0 Then IsFileWriteable = True 'Can write to file
Close FileNum
End Function[/VBA]

[lionne]

Thanks a lot, Kenneth!!!

Although the files were closed (I checked on that), the problem was in FreeFile indeed.. I moved that into the loop, and it did the miracle:

[VBA]
For Each objFile In objFiles
iFileNum = FreeFile()
If Right(objFile.Name, 3) = strExt Then
'open the file to read lines
Open objFile.Path For Input As iFileNum
'loop through the lines
Do While Not EOF(iFileNum)

Loop
Close iFileNum
iFileNum = iFileNum + 1
End If
Next_objFile:

Next objFile
[/VBA]

Thanks again!

[Paul_Hossler]

Once you have a file handle, I think you can just keep reusing it so long as you close the file before opening one with the same handle

Just commenting out

[VBA]
iFileNum = iFileNum + 1
[/VBA]

might work

Paul

[lionne]

it does! Thanks guys, you're great!

[Paul_Hossler]

Glad you got it working the way you needed

From the 2007 online help...

FreeFile[(rangenumber)]

The optional rangenumber argument is a Variant that specifies the range from which the next free file number is to be returned. Specify a 0 (default) to return a file number in the range 1 – 255, inclusive. Specify a 1 to return a file number in the range 256 – 511.

If you have LOT of files, you might bump up against the limit, but reusing the file handle should avoid that

Paul

[snb]

You can avoid this using:


[vba]Sub M_snb()
folder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder:", 0).self.Path
With CreateObject("Scripting.FileSystemObject")
For Each fl In .getfolder(folder).Files
If Right(fl.Name, 4) = ".log" Then c00 = .opentextfile(fl.Path).readall
Next
End With
End Sub[/vba]

NB. Did you know you can read a file in 1 go in the freefile method using:


[vba]

c00=Input(LOF(1),1)

[/vba]