Import specific lines from text file into Excel spreadsheet using VBA

Question

I'm trying to get a macro setup that will import specific lines from a text file into a Excel spreadsheet. I am currently using the instr function to locate a specific word then read how many letters over I need to import data into the cells.

The reason I am doing it this way is due to the file being over 3500 lines and is not delimited or comma separated in any sense. Some of the data is the same as well which I run into problems with the above tactic.

What I need help with is how to import only like 20 specific lines into the spreadsheet(multiple sheets will be used, but can reuse this code), while using a technique like I mentioned earlier so I can decide where its reading from.

Thanks!

You could read the file line by line and check each line individually. See for example this recent question. — arcadeprecinct

BigElittles BigElittles · Accepted Answer · 2016-07-12T13:34:48

This is what I use constantly. Just replace the file location with your text file and this little blip will read it line by line. Then you can throw logic against a single line and decide what to do with it. Its a good solution when your dealing with data that regex statements can't handle accurately.

Sub LoadSettings()
Dim fso As Scripting.FileSystemObject
Dim F As File
Dim F2 As TextStream
Dim TS As TextStream
Dim lngCount As Long


Set fso = New Scripting.FileSystemObject

fileloc = "Whereyourtextfileislocated"
Set F = fso.GetFile(fileloc)


Set TS = F.OpenAsTextStream(1, -2)

S = ""
Do

RptText = TS.ReadLine

if rpttext = whatever criteria, or even if instr(rpttext,"whateveryouwant")     >0 then
    do something with rpttext
end if

Loop

End Sub

Import specific lines from text file into Excel spreadsheet using VBA

1 Answers