69

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:

*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 A2 for the first path, B2 for the next, etc.

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?
Improve this question
0

5 Answers 5

Reset to default
90

for the most basic read of a text file, use 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

#Author note - Please stop adding in close #FileNum - it's addressed in the comments, and it's not needed as an improvement to this answer

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I'm a little late to the party, but Line Input has problems with anything other than strictly a CR or CRLF combination (i.e. LF on its own) - FSO has no such problems (but yeah, is probably a little bit slower)
Remember Close #FileNum at the end!
For anyone else wondering: DataLine excludes the terminating CR or CRLF (source)
ON ERROR is required.
um, from what I understand, it's a really bad idea to leave out the CLOSE command.
|
56

I find the FileSystemObject with a TxtStream the easiest way to read files

Dim fso As FileSystemObject: Set fso = New FileSystemObject
Set txtStream = fso.OpenTextFile(filePath, ForReading, False)

Then with this txtStream object 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.

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.

Improve this answer

4 Comments

thanks for the reply Brad. I think this also will accomplish what I wanted just fine
Actually, without dimensioning txtStream as a TextStream object, there will be no intellisense
You can also do this without checking the Microsoft Scripting Runtime box by using Set fso = CreateObject("Scripting.FileSystemObject") instead of Dim fso As FileSystemObject: Set fso = New FileSystemObject, you just lose the intellisense
WIthout explaining how to use the args to fos it is useless... So real example is : fso.OpenTextFile(.SelectedItems(1), 1, 0, -2) 1 for Read 0 for "do not create (obviously we are reading an existing file) -2 for Default Encoding bsed on OS. You are welcome. learn.microsoft.com/en-us/office/vba/language/reference/…
43

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
Improve this answer

2 Comments

This is the correct answer. You can do the same in FileSystemObject, but FSO will often fail to run due to security restrictions, and it is significantly slower.
... but assumes the file will fit into memory
2

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
Improve this answer

Comments

-2

The below is my code from reading text file to excel file.

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
Improve this answer

2 Comments

can you post as text instead of a image?
jeffld, I uploaded the string

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.