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.

Ansgar Wiechers · Accepted Answer · 2017-10-18T14:35:22.227

3

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

edited Oct 18 '17 at 14:35

answered Oct 18 '17 at 01:14

Ansgar Wiechers

193,178
25
254
328

That makes sense. Thank you much. – Quintin Oct 18 '17 at 12:08
@lankymart I put the first code snippet in a blockquote to indicate that it's quoted from the question (minus the "underlining" of course). – Ansgar Wiechers Oct 18 '17 at 14:36

Opening Each File in a Loop

1 Answers1