Reading an entire text file using vba

Question

I am trying to read a text file using vba. I tried the code below

Open "C:\tester.txt" For Input As #1 Worksheets("UI").Range("H12").Value = Input$(LOF(1), 1) Close #1 

When I run this, I get an error.

Runtime Error '62'. Enter the end of the file.

Text file content:

Unable to open COM10. Make sure it is connected
Plus other stuff
And much more the way more things

Thanks in advance for your help.

Answer 1

The following code will go through each line of a text document and print them with a range of H12 and lower in the user sheet.

 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 

Answer 2

Instead of looping through a cell, you can read the entire file into an array of options, and then output it in one go.

Change the path from C:\temp\test.txt to the appropriate one.

 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 

Answer 3

More A bit changed for those who do not like that VBA should compose explicit variables, and then spend time transferring data. Let With. do the job

 Function LoadFileStr$(FN$) With CreateObject("Scripting.FileSystemObject") LoadFileStr = .OpenTextFile(FN, 1).readall End With End Function 

Answer 4

Reply to brettdj slightly adjusted

 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 

Answer 5

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 

Answer 6

 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 

Answer 7

I think a simpler alternative is Data > From Text , and you can specify how often the data is updated in the properties.