How to search through csv files in a folder for a string through excel vba?

Question

I am using excel vba to find a record in csv files which matches given string I need help in searching through all csv files in a folder for a particular string and get the file name which contains the search string and from within the file the row itself. The string will always appear in column B of the CSV. Thanks in advance

If you have any code you should post it. If you haven't yet tried anything, try using Dir(pathToYourFolder & "*.csv") to loop through and open each file in the source folder. Plenty of examples of this here on SO alone. — Tim Williams
– Tim Williams, Commented Mar 13, 2014 at 16:48

Mark Setchell · Accepted Answer · 2014-03-13 16:51:59Z

6

It might be easier to start a Command Prompt and navigate to your folder containing the CSV files. Then you can do this:

FINDSTR /N /I "something" *.CSV

where "something" is whatever you are looking for.

answered Mar 13, 2014 at 16:51

Mark Setchell

210k32 gold badges310 silver badges505 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ichayan Over a year ago

How can i get this into VBA?

Mark Setchell Over a year ago

My point was that this probably easier than loading a load of files in Excel by clicking around all over the place.

user3075740 Over a year ago

seriously saved my time, ive been trying to bang head with powershell

ichayan · Accepted Answer · 2014-03-14 13:21:45Z

0

This returns the first file it finds the match, found here http://www.ggkf.com/excel/vba-macro-to-search-a-folder-for-keyword

Sub loopThroughFiles()
Dim file As String
file = FindFiles("putyourpathname eg.c:\reports", "search string")
If (file <> "") Then MsgBox file
End Sub
Function FindFiles(ByVal path As String, ByVal target As String) As String
' Run The Shell Command And Get Output
Dim files As String
files = CreateObject("Wscript.Shell").Exec("FINDSTR /M """ & target & """ """ & path &     "\*.*""").StdOut.ReadAll
FindFiles = ""
If (files <> "") Then
    Dim idx As Integer
    idx = InStr(files, vbCrLf)
    FindFiles = Left(files, idx - 1)
End If
End Function

answered Mar 14, 2014 at 13:21

ichayan

251 gold badge1 silver badge9 bronze badges

Collectives™ on Stack Overflow

How to search through csv files in a folder for a string through excel vba?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related