从文本文件- Excel中抽取行

Question

我有一个这样的文本文件，

文本：

-- Begin 
  Line1
  Line2
  Line3
      ^
-- Begin
  Line1
  Line2
  Line3
  Line4
      ^
.
.
.
.

基本上，我希望将Line1放在数组中的--Begin到^行之间，这样数组中的每个元素都是数组的一行示例。

Array =  [("Line1"  & vbNewLine & "Line 2") , ("Line1"  & vbNewLine & "Line 2" & vbNewLine & "Line 3") ... ]

但基本上想要将数组中的每个元素存储在一个单元格中。(甚至不需要使用数组) ..。

不确定这在excel VBA中是否可行，但这是我迄今为止尝试过的

    Dim FileNum As Integer
    Dim DataLine As String
    Dim Lines As Variant
    Dim j As Integer


    FileNum = FreeFile()
    Open "C:..." For Input As #FileNum


 While Not EOF(FileNum)
       Line Input #FileNum, DataLine

      If InStr(DataLine, "-- Begin") > 0 Then
        nextLinecounter = 1

      ElseIf InStr(DataLine, "^") > 0 Then
        nextLinecounter = 0
        j = j + 1

      ElseIf nextLinecounter = 1 Then
       Lines(j) = DataLine + .. Somehow concatenate next lines into array

     End If


Wend

我被困在如何跳过下一行，并附加到当前的条目，任何方式来完成这一感谢。

Answer 1

所以我会做一些不同的事情。使用更最新的方法来读取文件。

请参阅有关如何在VBA - here中读取here文件的详细信息。

注意:您需要通过VBE、Microsoft Scripting Runtime工具、->引用添加对->的引用。

Option Explicit

Sub ReadTxtFile()

    Dim oFSO As New FileSystemObject
    Dim oFS As TextStream

    Dim filePath As String
    filePath = "C:\Users\" & Environ$("username") & "\Desktop\foo.txt"

    If Not fileExist(filePath) Then GoTo FileDoesntExist

    On Error GoTo Err

    ReDim arr(0) As String
    Dim s As String

    Set oFS = oFSO.OpenTextFile(filePath)
    Do While Not oFS.AtEndOfStream
        Dim line As String
        line = oFS.ReadLine
        If InStr(line, "-- Begin") = 0 And InStr(line, "^") = 0 Then
            s = s & line
        End If
        If InStr(line, "^") > 0 Then
            arr(UBound(arr)) = s
            ReDim Preserve arr(UBound(arr) + 1)
            s = vbNullString
        End If
    Loop
    ReDim Preserve arr(UBound(arr) - 1)
    oFS.Close

    Dim k As Long
    For k = LBound(arr) To UBound(arr)
        Debug.Print k, arr(k)
    Next k
    Exit Sub

FileDoesntExist:
    MsgBox "File Doesn't Exist", vbCritical, "File not found!"
    Exit Sub

Err:
    MsgBox "Error while reading the file.", vbCritical, vbNullString
    oFS.Close
    Exit Sub

End Sub


Function fileExist(path As String) As Boolean
    fileExist = IIf(Dir(path) <> vbNullString, True, False)
End Function

foo.txt看起来像这样

-- Begin 
  Line1
  Line2
  Line3
      ^
-- Begin
  Line1
  Line2
  Line3
  Line4
      ^

您的数组如下所示

​

​