使用 vba 读取整个文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20390397/
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
reading entire text file using vba
提问by Sanket
I'm trying to read a text file using vba. I tried the below code
我正在尝试使用 vba 读取文本文件。我试过下面的代码
Open "C:\tester.txt" For Input As #1
Worksheets("UI").Range("H12").Value = Input$(LOF(1), 1)
Close #1
When I run this I'm getting an error.
当我运行这个时，我收到一个错误。
Run-time error '62'. Input past end of file.
运行时错误“62”。输入文件末尾。
The content of text file is:
文本文件的内容是：
Unable to open COM10. Make sure it is connected
Plus other stuff
And more stuff
way more stuff
无法打开 COM10。确保它已连接
加上其他东西
还有更多的东西
方式更多的东西
Thanks in advance for help.
预先感谢您的帮助。
采纳答案by Netloh
The following code will loop through each line in the text document and print these from range H12and downward in the UI-sheet.
以下代码将遍历文本文档中的每一行，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
回答by brettdj
Rather than loop cell by cell, you can read the entire file into a variant array, then dump it in a single shot.
您可以将整个文件读入一个变体数组，然后一次性将其转储，而不是逐个单元地循环。
Change the path from C:\temp\test.txtto suit.
将路径从 更改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
回答by Harry S
More Slightly modified for those who do not like VBA to have to make up explicit variables and then waste time transfer data to them.. Let With. do the job
对那些不喜欢 VBA 的人稍作修改，必须组成显式变量，然后浪费时间将数据传输给它们.. 随用随用。做这份工作
Function LoadFileStr$(FN$)
With CreateObject("Scripting.FileSystemObject")
LoadFileStr = .OpenTextFile(FN, 1).readall
End With
End Function
回答by Fidel
brettdj's answer, slightly adjusted
brettdj 的回答，略有调整
Public Function readFileContents(ByVal fullFilename As String) As String
Dim objFSO As Object
Dim objTF As Object
Dim strIn As String
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTF = objFSO.OpenTextFile(fullFilename, 1)
strIn = objTF.readall
objTF.Close
readFileContents = strIn
End Function
回答by Fidel
To read line by line:
要逐行阅读：
Public Sub loadFromFile(fullFilename As String)
Dim FileNum As Integer
Dim DataLine As String
FileNum = FreeFile()
Open fullFilename For Input As #FileNum
While Not EOF(FileNum)
Line Input #FileNum, DataLine
Debug.Print DataLine
Wend
End Sub
回答by dcromley
Sub LoadFile() ' load entire file to string
' from Siddharth Rout
' http://stackoverflow.com/questions/20128115/
Dim MyData As String
Open "C:\MyFile" For Binary As #1
MyData = Space$(LOF(1)) ' sets buffer to Length Of File
Get #1, , MyData ' fits exactly
Close #1
End Sub
回答by Slai
I think an easier alternative is Data> From Textand you can specify how often the data is refreshed in the Properties.
我认为更简单的替代方法是Data>From Text并且您可以在属性中指定刷新数据的频率。
回答by Marcelo Scofano
Fidel's answer, over Brettdj's answer, adjusted for ASCII or Unicode and without magical numbers:
Fidel 的答案，在 Brettdj 的答案之上，针对 ASCII 或 Unicode 进行了调整，并且没有神奇的数字：
Public Function readFileContents(ByVal fullFilename As String, ByVal asASCII As Boolean) As String
Dim objFSO As Object
Dim objTF As Object
Dim strIn As String
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTF = objFSO.OpenTextFile(fullFilename, IOMode:=ForReading, format:=IIf(asASCII, TristateFalse, TristateTrue))
strIn = objTF.ReadAll
objTF.Close
readFileContents = strIn
End Function
