VBA excel multiline string search

Question

I want to search a multi line string in a text file using VBA excel macro. I tried using InStr function.But its not working as I expected. My exact aim is to read a multi line string stored in a cell and check whether it is available in a text file. For that what i did is read the text file in to a variable, reading the string saved in the cell to another variable and comparing using Instr using binary comparison. Will InStr work for multi line string? If not any any other way to compare it?

This is my code

Public Function string_compare() As String
    Dim strFilename As String
    Dim strSearch As String
    strFilename = "D:\test.txt"
    Dim strFileContent As String
    Dim iFile As Integer: iFile = FreeFile
    Open strFilename For Input As #iFile
    strFileContent = Input(LOF(iFile), iFile)
    Close #iFile
    strSearch = Sheet1.Cells(9, 1).Value
    If InStr(1, strFileContent, strSearch, vbBinaryCompare) > 0 Then
        MsgBox "success"
    Else
        MsgBox "failed"
    End If
End Function

When I checked the strings both seems to be identical.Even though the strings are identical, the searching result always failing. Any suggestions will be helpful.

A multi-line string in an Excel cell will typically have vbLf (ASCII 10) as a line separator. A text file (on Windows) will typically be vbCrLf (ASCII 13 + ASCII 10). These will not match if you use Instr(), so you could try replacing the vbLf in the text from the cell with vbCrLf before using Instr() — Tim Williams
– Tim Williams, Commented Jul 2, 2016 at 6:40
As suggested by @TimWilliams, before using InStr function, write: strFileContent = Application.WorksheetFunction.Substitute(strFileContent, vbCrLf, "") and strSearch = Application.WorksheetFunction.Substitute(strSearch, vbLf, "") — Mrig
– Mrig, Commented Jul 2, 2016 at 6:43
VBA has a Replace() function since Excel 2000. If the line-breaks are significant then you should normalize them to the same version: if they're not significant then you can just remove them altogether. — Tim Williams
– Tim Williams, Commented Jul 2, 2016 at 6:47
Thank you very much Tim and Mrig. Its working fine as expected.How to make it answered?Hope it will help somebody else too. — user3710272
– user3710272, Commented Jul 2, 2016 at 15:17

user3710272 · Accepted Answer · 2016-07-03 05:14:45Z

As Tim and Mrig suggested I removed the cr and crlf from the text as follows. Now its working fine.I could use this for comparing multi line strings.I am posting my code segment here.Hope it may help somebody else too.

Public Function stringcompare(sourcefile As String, Workbookname As Worksheet) As String
Dim strSearch As String
Dim strFileContent As String
Dim iFile As Integer: iFile = FreeFile
Open sourcefile For Input As #iFile
strFileContent = Input(LOF(iFile), iFile)
Close #iFile
strSearch = Workbookname.Cells(1, 1).Value
strFileContent = Application.WorksheetFunction.Substitute(strFileContent, vbCrLf, "")
strSearch = Application.WorksheetFunction.Substitute(strSearch, vbLf, "")
    If StrComp(strFileContent, strSearch, vbBinaryCompare) = 0 Then
        MsgBox "success"
    Else
        MsgBox "failed"
    End If
End Function

Collectives™ on Stack Overflow

VBA excel multiline string search

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related