时间戳

首页 > 解决方案 > 搜索字符串并返回行号 VBA

excel - 搜索字符串并返回行号 VBA

问题描述

我对 VBA Excel 编程很陌生。我有一个文本文件，其中某些点的 x、y、z 坐标。它在开始时也有一些不需要的信息。我所需的信息仅出现在字符串“1-xxx”之后的第 20 行。我的代码是打开这个文件，逐行遍历，搜索字符串'1-xxx'并返回行号。然后将此行号输出作为输入提供给下方“.TextFileStartRow”字段中记录的宏。我在互联网上搜索并写了这样的东西。但问题是我只得到搜索文本的字符位置，而不是我预期的行号。我哪里错了？

    Sub Macro()

    Dim Pos_rw As Integer, text As String, textline As String
    Dim folder As String, StartingDir As String
    Dim file
    StartingDir = ThisWorkbook.Path
    ChDir StartingDir
    file = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    Open file For Input As #1
    Do Until EOF(1)
     Line Input #1, textline
     text = text & textline
    Loop
    Close #1
    Pos_rw = InStr(text, "1-xxx")

    With ActiveSheet.QueryTables _
        .Add(Connection:="TEXT;" & file, Destination:=Range("$D$2:$F$26"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = Pos_rw
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = ":"
        .TextFileColumnDataTypes = Array(9, 9, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
  End With

标签： excelvba

解决方案

解决方案是跟踪循环中的行号，一旦您找到您正在寻找的行，它就会终止：

Sub Macro()
    Dim Pos_rw As Long
    Dim textline As String
    Dim folder As String, StartingDir As String
    Dim found As Boolean

    Dim file
    StartingDir = ThisWorkbook.Path
    ChDir StartingDir
    file = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    Open file For Input As #1

    Do Until EOF(1) or found
        Pos_rw = Pos_rw + 1
        Line Input #1, textline
        If InStr(textline, "1-xxx") > 0 Then found = True
    Loop
    Close #1
    'the rest of your logic, including some safety code if found = False here
End Sub

一个小点：我改成Dim Pos_rw As Integer了Dim Pos_rw As Long. 在现代 VBA中使用几乎没有充分的理由Integer（除非您以某种方式或其他方式在 16 位处理器上运行它）。您无缘无故地冒着溢出的风险。

推荐阅读