将.txt文件中的单独行和列导入excel工作表（vba）

I have been following an example for importing .txt files into excel, the only problem is i can't seem to figure out how to make this line split the data into 2 separate columns

我一直在关注将.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

The .txt file I need to import has 2 columns and N rows , my question is , how can I read it without both columns from the .txt file being written into 1 column on the excel worksheet?

我需要导入的.txt文件有2列和N行,我的问题是,如果没有将.txt文件中的两列写入excel工作表上的1列,我该怎么读?

N
X  Y
X2 Y2
X3 Y3
etc..

Thats how the .txt file must look. Thanks in advance.

那是.txt文件必须看起来如何。提前致谢。

1 个解决方案

#1

This will do the job, using VBA Split() function:

这将使用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

Content of MyTestFile.txt:

50
info    computer
image   logo
tendancy    soulant

Excel Result: