vba - 将 .txt 文件中的单独行和列导入 Excel 工作表(vba)

Question

我一直在遵循将 .txt 文件导入 Excel 的示例，唯一的问题是我似乎无法弄清楚如何使这一行将数据拆分为 2 个单独的列

Set oFS = oFSO.OpenTextFile(filePath)
Do While Not oFS.AtEndOfStream
Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1) = oFS.ReadLine
Loop
oFS.Close

我需要导入的 .txt 文件有 2 列和 N 行，我的问题是，如何在不将 .txt 文件中的两列都写入 Excel 工作表上的 1 列的情况下读取它？

N
X  Y
X2 Y2
X3 Y3
etc..

这就是 .txt 文件的外观。提前致谢。

Answer 1

这将使用 VBA Split() 函数完成这项工作:

Sub sof20301663ImportTextFile()
  Const ForReading = 1, ForWriting = 2, ForAppending = 3

  Dim i, iup, varArray
  Dim fso As Object, tso As Object
  Dim strFilePath, strLine      

  ' adapt here your text file name:      '
  strFilePath = "MyTestFile.txt"

  Set fso = CreateObject("Scripting.FileSystemObject")      '
  ' get TextStream:

  Set tso = fso.OpenTextFile(strFilePath, ForReading)

  i = 2
  Do While Not tso.AtEndOfStream        '
    ' read a line from the file:
    strLine = tso.ReadLine
    ' split with separator tab:
    varArray = Split(strLine, vbTab)
    iup = UBound(varArray)
    ' split with separator space:

    If (iup <= 0) Then
      varArray = Split(strLine, " ")
      iup = UBound(varArray)
    End If

    ' fill a cell: using strLine as range address:

    strLine = "A" & i
    Range(strLine).Value = varArray(0)
    Range(strLine).NumberFormat = "General"

    ' if there is more then two words, fill cell 2:

    If (iup > 0) Then
      strLine = "B" & i
      Range(strLine).Value = varArray(1)
      Range(strLine).NumberFormat = "General"
    End If        
    i = i + 1

  Loop

  ' clean objects:

  tso.Close
  Set tso = Nothing

  Set fso = Nothing
End Sub

MyTestFile.txt 的内容:

50
info    computer
image   logo
tendancy    soulant

Excel 结果: