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?
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
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
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.
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
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
Source: Stackoverflow.com