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

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:UsersmeDesktoptest")
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:UsersMEDesktoptest") '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.

1 Answer
1

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

file = Dir("C:UsersmeDesktoptest") writes the filename to file if the file "test" exists, if you use Dir("C:UsersmeDesktoptest") 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.

file = Dir("C:UsersmeDesktoptest")

file

Dir("C:UsersmeDesktoptest")

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.

If InStr(file, "test") > 0 Then

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.

.csv

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:UsersMeDesktoptest") '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.

If InStr(1, vbBinaryCompare) > 0 Then

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.

Range("A2:D200210").Clear

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.

for each

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.