VBA Excel多行字符串搜索 [英] VBA excel multiline string search

问题描述

我想使用VBA excel宏在文本文件中搜索多行字符串.我尝试使用InStr函数.但是它没有按预期工作.我的确切目的是读取存储在单元格中的多行字符串，并检查文本文件中是否可用.为此，我将文本文件读取到一个变量中，将保存在单元格中的字符串读取到另一个变量中，并使用Instr使用二进制比较进行比较.InStr是否适用于多行字符串?如果没有其他比较方法?

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?

这是我的代码

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.

推荐答案

按照Tim和Mrig的建议，我从文本中删除了cr和crlf，如下所示.现在可以正常工作了.我可以用它来比较多行字符串.我在这里发布我的代码段.希望它也可以对其他人有所帮助.

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

这篇关于VBA Excel多行字符串搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！