使用vba读取整个文本文件

以下代码将循环遍历文本文档中的每一行,H12并在UI工作表中从范围和向下打印这些代码.

Sub ImportFromText()
    Open "C:\tester.txt" For Input As #1
    r = 0
    Do Until EOF(1)
        Line Input #1, Data
        Worksheets("UI").Range("H12").Offset(r, 0) = Data
        r = r + 1
    Loop
    Close #1
End Sub

您可以将整个文件读取到变量数组中,然后将其转储为单个数据,而不是逐个单元循环.射击

将路径更改C:\temp\test.txt为适合.

Sub Qantas_Delay()
Dim objFSO As Object
Dim objTF As Object
Dim strIn 'As String
Dim X

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTF = objFSO.OpenTextFile("C:\temp\test.txt", 1)
strIn = objTF.readall
X = Split(strIn, vbNewLine)
[h12].Resize(UBound(X) + 1, 1) = Application.Transpose(X)
objTF.Close

End Sub

对于那些不喜欢VBA必须组成显式变量然后浪费时间将数据传输给他们的人来说,稍微修改了一下.做好这份工作

Function LoadFileStr$(FN$)

  With CreateObject("Scripting.FileSystemObject")

          LoadFileStr = .OpenTextFile(FN, 1).readall

        End With

End Function