excel - 使用 VBA 从文本中插入前 x 行

Question

我想从文本文件中插入前 x 行。我可以给出 StartRow 的编号，但是是否有这样的参数，我可以给出“EndRow”编号来给出我想要插入的前 x 行。

Sub insertTopX()

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\HarrsionDavid\Desktop\AnswerForEveryQuestions" _
        ,Destination:=Cells(1,1))
        .Name = "test_file.txt"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End Sub

Answer 1

您可以读取 txt 文件并按新行拆分。然后，您将拥有一个易于使用的数组。

示例文件:

读取文件并将其解析为变体；

通过startRow和 endRow ;

Option Explicit

Public Sub TestMe()

    Dim filePath As String
    filePath = "C:\Users\user\User\nt.txt"

    Dim myFile As String
    myFile = ReadFileLineByLineToString(filePath)

    Dim startRow As Long
    Dim endRow As Long
    Dim fixedFile As Variant

    fixedFile = Split(myFile, vbCrLf)

    startRow = 2
    endRow = 3

    Dim cnt As Long
    For cnt = startRow To endRow
        Debug.Print fixedFile(cnt - 1)
    Next cnt

End Sub

这是结果:



这是 ReadFileLineByLineToString :

Public Function ReadFileLineByLineToString(path As String) As String

    Dim fileNo As Long
    fileNo = FreeFile

    Open path For Input As #fileNo

    Do While Not EOF(fileNo)
        Dim textRowInput As String
        Line Input #fileNo, textRowInput
        ReadFileLineByLineToString = ReadFileLineByLineToString & textRowInput
        If Not EOF(fileNo) Then
            ReadFileLineByLineToString = ReadFileLineByLineToString & vbCrLf
        End If
    Loop

    Close #fileNo

End Function