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

New Member
Joined
Jun 28, 2019
Messages
1
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!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
Code:
Option Explicit

The problem you asked about is caused by these 2 lines
Code:
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

Code:
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
 
Last edited:

Forum statistics

Threads
1,175,862
Messages
5,899,918
Members
434,805
Latest member
Nihon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top