Get Data from Text File using VBA

Get Data from Text File using VBA

Function: Find text file in computer using VBA for select number of lines in the file

This function gets all the data from a text file located on a local drive or network. File is read using OpenAsTextStream method and the data is pulled out in a 2D array whose length is defined first. Data is read from a number of line you specify till the bottom of the column. You can call this function in any other sub routine by

a)       entering the complete file address (including the file name) in the first argument “FAddress” field of function. Example: “Thisworkbook.path & /MyFile.txt” and,

b)       entering the Line Number (integer) in the 2nd argument “StartRow” field of function. Example: “0” (use 0 for first row)

c)       entering the maximum number of columns (integer) in the 3rd argument “ColumNo” field of function.


Public Function GetDataFrmFile(FAddress As Variant, StartRow As Variant, ColumNo As Variant)

Application.ScreenUpdating = False

‘ Code Written & Published by Excelmodelers ( 19 Dec 12

Dim TS

Dim DataArr As Variant

Set fsD = CreateObject(“Scripting.FileSystemObject”)

Set MyFileD = fsD.GetFile(FAddress)

Set TS = MyFileD.OpenAsTextStream(ForReading)

Dim LnCnt As Integer

LnCnt = 0

Do While Not TS.AtEndOfStream

S1 = A1 & TS.ReadLine

LnCnt = LnCnt + 1



Set TS = Nothing


ReDim DataArr(LnCnt – 1 – StartRow, ColumNo)

Set TS = MyFileD.OpenAsTextStream(ForReading)

i = 0

k = 0

Dim SplitArrD As Variant

Do While Not TS.AtEndOfStream

SplitArrD = Split(TS.ReadLine, “,”)

If k >= StartRow Then

For jD = 0 To UBound(SplitArrD)

DataArr(i, jD) = SplitArrD(jD)

Next jD

i = i + 1

End If

k = k + 1



Set TS = Nothing

GetDataFrmFile = DataArr

End Function

Comments are closed.