Opening Each File in a Loop

Question

I'm writing a subroutine that needs to extract text from files in a directory. The routine is below. It works as long as there is only one file in the directory. When there are more than one, it tells me Set intFSO = intFSO.OpenTextFile(filePath, 1) line below.

I assume there is something I need to be doing to reset for the next file but I cannot seem to figure out what it is. Any tips?

Sub ExtractEDI(folPath)
  Dim sName, fil
  Dim intFSO
  Dim filePath

  Set intFSO = CreateObject("Scripting.FileSystemObject")

  For Each fil In fso.GetFolder(folPath).Files
    filePath = folpath & "\" & fil.Name
    Set intFSO = intFSO.OpenTextFile(filePath, 1)

    'will process file here

    intFSO.Close
  Next
  Set intFSO = Nothing
End Sub

There is more to this script. The routine above is called recursively in order to traverse the sub directories. All of that is working fine.

Answer 1

Set intFSO = intFSO.OpenTextFile(filePath, 1)
'   ^^^^^^   ^^^^^^

Don't replace your FileSystemObject instance with a file handle if you intend to use it again in the next iteration. Use a different variable for the file. And drop the whole path concatenation / OpenTextFile shebang. You can open the file directly from the File object.

This is all you need (assuming fso is a global FileSystemObject instance):

Sub ExtractEDI(folPath)
  For Each fil In fso.GetFolder(folPath).Files
    Set f = fil.OpenAsTextStream

    'will process file here

    f.Close
  Next
End Sub