Is the file open in excel?
If yes, then just use the .find method to search for "john does" and if it finds
it, look for 250 in the adjacent cell.
But I bet you meant that the .csv file was closed. This worked for me (win98
and xl2002). There's a DOS command called Find that can search through files.
Try "find /?" at a DOS prompt to see all the options.
I used /i to ignore upper/lower case differences
and /c to just count the number of strings found.
The command I want to run looks similar to this:
command.com /c find /i /c "john does, 250" "aba.csv" "res.txt"
If you look at the output of that file, it looks like:
---------- c:\my documents\excel\aba.csv: 1
I just look at the last 2 characters. If they're " 0", then the string wasn't
found. Anything else must have been.
I'm not sure if it'll work on your version of windows.
Option Explicit
Sub testme03()
Dim myCmd As String
Dim mySearchFileName As String
Dim myResultFileName As String
Dim myLine As String
Dim stringToFind As String
Dim myFileNum As Long
Dim FoundIt As Boolean
mySearchFileName _
= "c:\my documents\excel\aba.csv"
myResultFileName _
= "C:\my documents\excel\res" & Format(Now, "yyyymmdd_hhmmss") & ".txt"
stringToFind = "John Does, 250"
If Dir(mySearchFileName) = "" Then
MsgBox mySearchFileName & " doesn't exist"
Exit Sub
End If
myCmd = Environ("comspec") & " /c find /c /i " _
& Chr(34) & stringToFind & Chr(34) _
& " " & Chr(34) & mySearchFileName & Chr(34) _
& " " & Chr(34) & myResultFileName & Chr(34)
Shell myCmd
'Pause long enough for the Find to work.
Application.Wait Now + TimeSerial(0, 0, 3)
myFileNum = FreeFile()
Close #myFileNum
Open myResultFileName For Input As #myFileNum
FoundIt = False
Do While Not EOF(myFileNum)
Line Input #myFileNum, myLine
If Len(Trim(myLine)) 0 Then
If Right(myLine, 2) < " 0" Then
FoundIt = True
Exit Do
End If
End If
Loop
Close #myFileNum
Kill myResultFileName
If FoundIt Then
MsgBox "found it"
Else
MsgBox "nope"
End If
End Sub
Another way is to just open the file and look for it:
Option Explicit
Sub testme04()
Dim mySearchFileName As String
Dim myLine As String
Dim stringToFind As String
Dim myFileNum As Long
Dim FoundIt As Boolean
mySearchFileName _
= "c:\my documents\excel\aba.csv"
stringToFind = "John Does, 250"
If Dir(mySearchFileName) = "" Then
MsgBox mySearchFileName & " doesn't exist"
Exit Sub
End If
myFileNum = FreeFile()
Close #myFileNum
Open mySearchFileName For Input As #myFileNum
FoundIt = False
Do While Not EOF(myFileNum)
Line Input #myFileNum, myLine
If Len(Trim(myLine)) 0 Then
If InStr(1, myLine, stringToFind, vbTextCompare) 0 Then
FoundIt = True
Exit Do
End If
End If
Loop
Close #myFileNum
If FoundIt Then
MsgBox "found it"
Else
MsgBox "nope"
End If
End Sub
Untested: I would guess that the DOS Find version would be quicker for large
files. For small files, I'd use the second version.
Paul wrote:
Hi There,
I am creating a vba macro that would save my excel file
saved in csv (comma delimited). How could I search or
what is proper command in vba that would search a text
inside my text file. For example, I would like to search
this name "John Does, 250" in my "myfile.csv".
Appreciate very much your help.
Regards
Paul
--
Dave Peterson
