I begin with VBA and programmation.
I have a spreadsheet with X values. Each of this values match (or not) with an .xml file in a folder (the value is present in the xml title). What I need is that for each of these values my program search a matching .xml file and write "found" or "not found" next to the value in the spreadsheet.
My code so far :
Sub StringExistsInFile()
Dim theString As String
Dim path As String
Dim StrFile As String
Dim fso As New FileSystemObject
Dim file As TextStream
Dim line As String
theString = Sheets("PHILA_RESULT_PART_201703210429").Cells(i + 1, 2).Value
path = "C:\Users\Jira\Desktop\LaPoste\20170324_120939_export_phila_commande.Envoi1\"
StrFile = Dir(path & "*.xml")
i = 1
Do While StrFile <> ""
Set file = fso.OpenTextFile(path & StrFile)
Do While Not file.AtEndOfLine
line = file.ReadLine
If InStr(1, line, theString, vbTextCompare) > 0 Then
Sheets("PHILA_RESULT_PART_201703210429").Cells(i + 1, 14).Value = "found"
i = i + 1
Exit Do
End If
Loop
file.Close
Set file = Nothing
Set fso = Nothing
StrFile = Dir()
Loop
End Sub
Thanks for the help.
How the value are store in the spreadsheet :
In blue = the values I search. In red = where I want to write "found" or "not found".
Edit :
And there is my code after some "improvments"
Sub StringExistsInFile()
Dim theString As String
Dim path As String
Dim StrFile As String
Dim fso As New FileSystemObject
Dim file As TextStream
Dim line As String
theString = Sheets("PHILA_RESULT_PART_201703210429").Cells(i + 1, 2).Value
path = "C:\Users\Jira\Desktop\LaPoste\20170324_120939_export_phila_commande.Envoi1\"
StrFile = Dir(path & "*.xml")
i = 1
Do While Sheets("PHILA_RESULT_PART_201703210429").Cells(i + 1, 2).Value <> ""
Set file = fso.OpenTextFile(path & StrFile)
Do While Not file.AtEndOfLine
line = file.ReadLine
If InStr(1, line, theString, vbTextCompare) > 0 Then
Sheets("PHILA_RESULT_PART_201703210429").Cells(i + 1, 14).Value = "found"
Else
Sheets("PHILA_RESULT_PART_201703210429").Cells(i + 1, 14).Value = "not found"
End If
Loop
i = i + 1
file.Close
Set file = Nothing
StrFile = Dir()
Loop
Set fso = Nothing End Sub
Set fso = Nothingright beforeEnd Sub