VBA For Excel (CSV), Looping through files to get row, then appending rows into one List

Question

I am having trouble coding this VBA Macro for a bunch of CSV files (10000). After searching I found/used this for my code: Loop through files in a folder using VBA? . It doesn't seem to work and I'm not sure why... I have tried the While loop but it is very slow I don't know if it can finish running.

Sub LoopThroughFiles()
Dim MyObj As Object, MySource As Object, file As Variant
file = Dir("C:\Users\me\Desktop\test")
While (file <> "")
  If InStr(file, "test") > 0 Then
        '// my macro code is here
     Exit Sub
  End If
 file = Dir
Wend
End Sub

What else should I try changing? Where did I go wrong? I have also tried using this code https://www.thespreadsheetguru.com/the-code-vault/2014/4/23/loop-through-all-excel-files-in-a-given-folder but am unsure what else to change besides the directory and the 'Change First Worksheet's Background Fill Blue.

Also tried this http://www.ozgrid.com/VBA/loop-through.htm which seems pretty fool proof but I cant get it to work...

UPDATES FROM L8N

Option Explicit

Sub looper()
Dim fso As Scripting.FileSystemObject
Dim aFolder As Scripting.Folder
Dim aFile As Scripting.file
Dim aText As Scripting.TextStreame
Dim singleLine As String

Set fso = New FileSystemObject
Set aFolder = fso.GetFolder("C:\Users\ME\Desktop\test") 'set path to the folder that contains the files

For Each aFile In aFolder.Files 'loops through every file in the top level of the folder
    If InStr(1, vbBinaryCompare) > 0 Then
        Range("A2:D200210").Clear   'what i want to happen to every file
        Set aText = fso.OpenTextFile(aFile.Path, ForReading)
        Do Until aText.AtEndOfStream
            singleLine = aText.ReadLine 'read line into string, every call advances the line counter by one, this prevents skipping lines
            If InStr(1, singleLine, vbBinaryCompare) > 0 Then Debug.Print singleLine ' in line case, prints line if target value is found
        Loop
    End If
Next aFile
Debug.Print "finished"

End Sub

It runs, but it does not seem to implement the changes I want (Range("A2:D200210").Clear ) to each file. Also the string name for my code does not matter, the info in the sheet does not either. My original code was to test if it looped at all.

Answer 1

I don't know exactly what you are trying to do, the code you have does the following:

file = Dir("C:\Users\me\Desktop\test") writes the filename to file if the file "test" exists, if you use Dir("C:\Users\me\Desktop\test\") the function will return the name of the first file it finds.
On subsequent runs it will return the next file in the folder, keep in mind that this is a global call, so if you call the function somewhere else it may interfere. Using the Microsoft Scripting Engine Runtime is preferable in most cases apart from quick checks if a file exists.

If InStr(file, "test") > 0 Then You test if "test" is a part of the filename, so far so good, but keep in mind to tell InStr how it should compare the two strings. InStr accepts four parameters (all of them optional), be sure to pass the proper ones. The microsoft documentation is actually quite decent.

Is this what you wanted? I think you might be looking for something inside the .csv file, if so I can extend the script below.

A simple way to loop though all files in a folder is attached below:

Option Explicit

Sub looper()
    Dim fso As Scripting.FileSystemObject
    Dim aFolder As Scripting.Folder
    Dim aFile As Scripting.file
    Dim aText As Scripting.TextStream
    Dim targetName As String 'string that identifies files
    Dim targetWord As String 'string that identifies line inside csv file
    Dim singleLine As String 

    Set fso = New FileSystemObject
    Set aFolder = fso.GetFolder("C:\Users\Me\Desktop\test") 'set folder that contains the files
    targetName = "someFileName"
    targetWord = "someString"

    For Each aFile In aFolder.Files 'loops through every file in the top level of the folder
        If InStr(1, aFile.Name, targetName, vbBinaryCompare) > 0 Then
            Debug.Print "Found a matching File: "; aFile.Name
            Set aText = fso.OpenTextFile(aFile.Path, ForReading)
            Do Until aText.AtEndOfStream 
                singleLine = aText.ReadLine 'read line into string, every call advances the line counter by one, this prevents skipping lines
                If InStr(1, singleLine, targetWord, vbBinaryCompare) > 0 Then Debug.Print singleLine ' in line case, prints line if targer value is found
            Loop
        End If
    Next aFile
    Debug.Print "finished"
End Sub

Bonus Info: Use option explicit to make sure all variables are declared properly

---

Edit:

Not able to add comments to your post yet, so I'll put the response here.

If InStr(1, vbBinaryCompare) > 0 Then this line is now broken as it will always return 0. If you want to loop through every file just omit the IF-Contitional or set it to If True Then.

Range("A2:D200210").Clear is a so called implicit reference, the Range Object refers to the "Global" Worksheet. Every time this piece of code is executed, the change happens on the "Global" Worksheet, a nice answer by Mathieu Guindon from just recently explains this.

It runs, but it does not seem to implement the changes I want (Range("A2:D200210").Clear ) to each file. Also the string name for my code does not matter, the info in the sheet does not either. My original code was to test if it looped at all.

So from what I can see you try to delete everything but the first row inside a .csv file. A .csv file is not a worksheet(even though you can import it into excel), so you can't use the Range property.
Fortunately, there is an even easier way to do this, just use the Microsoft Scripting Runtime to edit the .csv file.

Set aText = aFile.OpenAsTextStream(ForReading) ' open file in read mode
singleLine = aText.ReadLine ' read the first line and store it
Set aText = aFile.OpenAsTextStream(ForWriting) ' open file in write mode
aText.Write (singleLine) 'write the line you saved before

Or even more compact:

aFile.OpenAsTextStream(ForWriting).Write aFile.OpenAsTextStream(ForReading).ReadLine 'overwrites the file with what was written in the first line.

The advantage with the longer code is the ability to use the string somewhere else, for example storing it somewhere in your workbook.