vba:convert text files into excel workbooks, open them, and then call a macro on another module. Then do the same thing, looping through text files in

[amontalto]

Using VBA, I am trying to convert text files into excel workbooks, open them, and then call a macro on another module (Call DataCleanBehfMRI). Then do the same thing, looping through text files in other subfolders.
Folder structure: V:\Behavioral\Twin_behaviorTry\19510196-1\WorkingMemory.txt V:\Behavioral\Twin_behaviorTry\19510197-1\WorkingMemory.txt etc.
So each "WorkingMemory.txt" file contain data that I want to run Macros on, for each subject (19510196-1,19510197-1, etc.).
I tried a code to loop through individual files, within multiple folders and tried to adapt it with text files but it just skips the code from "Workbooks.OpenText filename:=folderPath & filename, _" to "end if".
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub LoopSubfoldersAndFiles()
Dim foo As Object
Dim folder As Object
Dim subfolders As Object
Dim MyFile As String
Dim wb As Workbook
Dim CurrFile As Object

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("V:\Behavioral\Twin_behaviorTry")
Set subfolders = folder.subfolders
MyFile = "*.txt"

For Each subfolders In subfolders

Set CurrFile = subfolders.Files

For Each CurrFile In CurrFile
If CurrFile.Name = MyFile Then
Workbooks.OpenText filename:=folderPath & filename, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
Set wb = ActiveWorkbook

'Call a subroutine here to operate on the just-opened workbook
Call DataCleanBehfMRI
'close open work book

wb.Close SaveChanges:=False

filename = Dir
End If
Next

Next

Set fso = Nothing
Set folder = Nothing
Set subfolders = Nothing

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub</code>It is quite complex but I would much appreciate suggestions!
Many thanks!

 

[Yongle]

There are some other errors in your code that you have not spotted
Suggest you insert this at the TOP of your module which will help

Option Explicit

The problem you asked about is caused by these 2 lines

For Each subfolders In subfolders
For Each CurrFile In CurrFile

You need 2 additional variables for the individual member in each collection

When testing I use Debug.Print to print to the immediate window so that I can see what VBA is doing
- delete after testing
- make immediate window visible in VBA with {CTRL} G

For Each [COLOR=#ff0000]subFolder[/COLOR] In [COLOR=#006400]subfolders[/COLOR]
    Debug.Print "SUBFOLDER", subFolder
    
    Set CurrFile = subFolder.Files
    For Each [COLOR=#ff0000]aFile[/COLOR] In [COLOR=#006400]CurrFile[/COLOR]
        Debug.Print "FILE", aFile

    Next aFile

Next subFolder

It is good practice to use the variable name after Next when placing loops inside loops
- easier to see what the code is doing

I have not tested your code