Writing data from text file to Excel cells

Posted on December 10, 2019 by

There are quite a few methods for writing data from a text file to Excel. The main are the following:

Through a Query Table
Through opening the text file and
- Reading and writing line by line
- Reading the text file, writing it to an array and writing from the array

Writing data from text file to Excel through a Query Table.

Writing the file through a query table has one advantage – it provides the possibility to update the data from the Excel interface. This is useful, as far as the end users should not go to the scary #VBA once the text file is changed:

In the code below, the file from which the data is taken is on the Desktop. It is named file.txt:

Sub QueryTableMethod()

    Dim fileName As String
    fileName = "C:\Users\" & Environ("Username") & "\Desktop\file.txt"
    Worksheets(1).Cells.Delete
    
    With Worksheets(1).QueryTables.Add(Connection:="Text;" & fileName, Destination:=Worksheets(1).Range("C4"))
        .Name = "QueryTableMethod"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True  'False to bold the first line
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

Sub QueryTableMethod()

Dim fileName As String

fileName = "C:\Users\" & Environ("Username") & "\Desktop\file.txt"

Worksheets(1).Cells.Delete

With Worksheets(1).QueryTables.Add(Connection:="Text;" & fileName, Destination:=Worksheets(1).Range("C4"))

.Name = "QueryTableMethod"

.FieldNames = False

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True 'False to bold the first line

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 437

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = False

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

End Sub

Writing data from text file to Excel. Reading and writing line by line.

This is a rather simple method, and the loop is supposed to be slow. The slowness comes from the fact, that the writing to the Excel file is written line by line. Up to a few hundred lines, the speed should be ok. For more, try to write it all at once, with the next method.

Sub LoopingThroughLinesMethod()

    Dim myFile As Long
    Dim textLine As String
    Dim fileName As String

    fileName = "C:\Users\" & Environ("Username") & "\Desktop\file.txt"

    myFile = FreeFile()
    Open fileName For Input As myFile

    Dim writing As String
    Dim i As Long: i = 4
    While Not EOF(myFile)
        Line Input #myFile, textLine
        Worksheets(1).Cells(i, "B").Value = textLine
        i = i + 1
    Wend
    
    Close myFile

End Sub

Sub LoopingThroughLinesMethod()

Dim myFile As Long

Dim textLine As String

Dim fileName As String

fileName = "C:\Users\" & Environ("Username") & "\Desktop\file.txt"

myFile = FreeFile()

Open fileName For Input As myFile

Dim writing As String

Dim i As Long: i = 4

While Not EOF(myFile)

Line Input #myFile, textLine

Worksheets(1).Cells(i, "B").Value = textLine

i = i + 1

Wend

Close myFile

End Sub

Writing data from text file to Excel. Reading the text file, writing it to an array and writing from the array.

This method is pretty much like the previous one, but with some advanced VBA features. Through this, the writing of the Notepad data to Excel is done on one single step. As you are probably aware, writing everything at once is quite faster than writing line by line. The advanced features that are used are:

Get
Space() Function
Write array to a range with 1 line - Range("D1").Resize(UBound(myArray()) + 1) = Application.Transpose(myArray)

Sub WriteToArray()
    
    Dim wks As Worksheet: Set wks = Worksheets(1)
    wks.Cells.Delete
    
    Dim fileName As String
    fileName = "C:\Users\" & Environ("Username") & "\Desktop\file.txt"

    Dim myData As String
    Dim myArray() As String
    Dim fileNumber As Long: fileNumber = FreeFile() '#1
    
    Open fileName For Binary As FreeFile()
    Dim lengthOfFile As Long: lengthOfFile = LOF(1)
    myData = Space(lengthOfFile)
    Get fileNumber, , myData
    Close fileNumber
    
    myArray() = Split(myData, vbCrLf)
    wks.Range("D1").Resize(UBound(myArray()) + 1) = Application.Transpose(myArray) '+1 is needed if the cursor ends on the last line
    
End Sub

Sub WriteToArray()

Dim wks As Worksheet: Set wks = Worksheets(1)

wks.Cells.Delete

Dim fileName As String

fileName = "C:\Users\" & Environ("Username") & "\Desktop\file.txt"

Dim myData As String

Dim myArray() As String

Dim fileNumber As Long: fileNumber = FreeFile() '#1

Open fileName For Binary As FreeFile()

Dim lengthOfFile As Long: lengthOfFile = LOF(1)

myData = Space(lengthOfFile)

Get fileNumber, , myData

Close fileNumber

myArray() = Split(myData, vbCrLf)

wks.Range("D1").Resize(UBound(myArray()) + 1) = Application.Transpose(myArray) '+1 is needed if the cursor ends on the last line

End Sub

Cheers! 🙂

Tagged with: Data Transfer, from text file, LoopThroughLines, QueryTables.Add, to excel cells, Writing data