在 VBA 中逐行读取/解析文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11528694/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Read/Parse text file line by line in VBA
提问by dancran
I'm trying to parse a text document using VBA and return the path given in the text file.
For example, the text file would look like:
我正在尝试使用 VBA 解析文本文档并返回文本文件中给出的路径。
例如，文本文件将如下所示：
*Blah blah instructions
*Blah blah instructions on line 2
G:\Folder\...\data.xls
D:\AnotherFolder\...\moredata.xls
I want the VBA to load 1 line at a time, and if it starts with a *then move to the next line (similar to that line being commented). For the lines with a file path, I want to write that path to cell, say A2for the first path, B2for the next, etc.
我希望 VBA 一次加载 1 行，如果它以 a 开头，*则移动到下一行（类似于被注释的那一行）。对于带有文件路径的行，我想将该路径写入单元格，例如A2第一个路径、B2下一个路径等。
The main things I was hoping to have answered were:
1. What is the best/simple way to read through a text file using VBA?
2. How can I do that line by line?
我希望回答的主要问题是：
1. 使用 VBA 阅读文本文件的最佳/简单方法是什么？
2. 我怎样才能逐行做到这一点？
回答by SeanC
for the most basic read of a text file, use open
对于最基本的文本文件读取，使用 open
example:
例子：
Dim FileNum As Integer
Dim DataLine As String
FileNum = FreeFile()
Open "Filename" For Input As #FileNum
While Not EOF(FileNum)
Line Input #FileNum, DataLine ' read in data 1 line at a time
' decide what to do with dataline,
' depending on what processing you need to do for each case
Wend
回答by Brad
I find the FileSystemObject with a TxtStream the easiest way to read files
我发现带有 TxtStream 的 FileSystemObject 是读取文件的最简单方法
Dim fso As FileSystemObject: Set fso = New FileSystemObject
Set txtStream = fso.OpenTextFile(filePath, ForReading, False)
Then with this txtStreamobject you have all sorts of tools which intellisense picks up (unlike using the FreeFile()method) so there is less guesswork. Plus you don' have to assign a FreeFile and hope it is actually still free since when you assigned it.
然后有了这个txtStream对象，你就拥有了智能感知收集的各种工具（与使用该FreeFile()方法不同），因此猜测工作更少。另外，您不必分配一个 FreeFile 并希望它在您分配它时实际上仍然是免费的。
You can read a file like:
您可以读取如下文件：
Do While Not txtStream.AtEndOfStream
txtStream.ReadLine
Loop
txtStream.Close
NOTE: This requires a reference to Microsoft Scripting Runtime.
注意：这需要对 Microsoft Scripting Runtime 的引用。
回答by Alex K.
For completeness; working with the data loaded into memory;
为了完整性；处理加载到内存中的数据；
dim hf As integer: hf = freefile
dim lines() as string, i as long
open "c:\bla\bla.bla" for input as #hf
lines = Split(input$(LOF(hf), #hf), vbnewline)
close #hf
for i = 0 to ubound(lines)
debug.? "Line"; i; "="; lines(i)
next
回答by satheesh kumar
You Can use this code to read line by line in text file and You could also check about the first character is "*" then you can leave that..
您可以使用此代码逐行读取文本文件中的行，您还可以检查第一个字符是“*”，然后您可以保留它..
Public Sub Test()
Dim ReadData as String
Open "C:\satheesh\myfile\file.txt" For Input As #1
Do Until EOF(1)
Line Input #1, ReadData 'Adding Line to read the whole line, not only first 128 positions
If Not Left(ReadData, 1) = "*" then
'' you can write the variable ReadData into the database or file
End If
Loop
Close #1
End Sub
回答by Tarun Reddy
The below is my code from reading text file to excel file.
下面是我从读取文本文件到excel文件的代码。
Sub openteatfile()
Dim i As Long, j As Long
Dim filepath As String
filepath = "C:\Users\TarunReddyNuthula\Desktop\sample.ctxt"
ThisWorkbook.Worksheets("Sheet4").Range("Al:L20").ClearContents
Open filepath For Input As #1
i = l
Do Until EOF(1)
Line Input #1, linefromfile
lineitems = Split(linefromfile, "|")
For j = LBound(lineitems) To UBound(lineitems)
ThisWorkbook.Worksheets("Sheet4").Cells(i, j + 1).value = lineitems(j)
Next j
i = i + 1
Loop
Close #1
End Sub
