Read Parse text file line by line in VBA

Question

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

User · Answer

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

User · Answer

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

User · Answer

for the most basic read of a text file  use open example  Dim FileNum As Integer Dim DataLine As String  FileNum   FreeFile   Open  quot Filename quot  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

User · Answer

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

User · Answer

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

[excel] Read/Parse text file line by line in VBA

Examples related to excel

Examples related to vba