Excel VBA – Read And Write Text Files

Share this

June 17, 2012

Last updated: 16/03/2018, 1 min read (without the code)

Excel VBA – Read And Write Text Files


Introduction


The integration between Excel and text files is still an important issue. The input and output of many dated programs are usually a text file (for example various calculation algorithms written in FORTRAN language). So, if you have a spreadsheet and you need somehow to write data to a text file or read/import data from a text file the following VBA code snippets will help you automate the procedure.

We will analyze 4 different methods, two for wirting and two for reading a text file:
Write: There are two ways of writing data to a text file: you can either use Write or Print statement. The output of Write statement is a line in which the data values are separated by commas, while hash marks (#) are around dates and quotes (“) around strings. The output of Print statement is a line that is more suitable for printing, with data values being arranged in columns separated by tab or any other delimited character. 

Read: As with the write procedure there are also two ways of reading data from a text file: you can either use Input or Line Input statement. With the Input statement, data values are read like a list of variables. Input statement is used for data that were produced with Write statement. On the other hand, with the Line Input statement all the data are read as a single string, so Line Input is suitable for reading data values produced by Print statement.


VBA code


VBA code for writing to a text file using the Write statement:

Sub WriteTextFile()

'------------------------------------------------------------------------------------------
'With WriteTextFile macro you can write data to a text file in which the data values are
'separated by commas, while hash marks (#) are around dates and quotes (") around strings.

'Written By: Christos Samaras
'Date: 17/06/2012
'Last Updated: 16/03/2018
'E-mail: [email protected]
'Site: https://myengineeringworld.net/////
'------------------------------------------------------------------------------------------

'Declaring the necessary variables.
Dim sht As Worksheet
Dim fName As String
Dim fNumber As Integer
Dim counter As Long
Dim lastRow As Long

'The next variables are related to the specific data that exist in the worksheet.
Dim drawing As String
Dim weld As String
Dim welder As String
Dim pipeSize As Integer
Dim weldingDate As Date
Dim pipeMaterial As String

'Set the sheet containing the data.
Set sht = ThisWorkbook.Sheets("Original Data")

'Find the last row that contains data.
With sht
.Activate 'Just showing the active sheet. It is not necessary.
lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
End With

'Set the name and the path of text file that will be created based on the workbook path.
fName = ThisWorkbook.Path & "Excel Data (Write).txt"

'Get an unused file number.
fNumber = FreeFile

'Create a new file (or overwrite an existing one).
Open fName For Output As #fNumber

'Loop through each row (aside headers).
For counter = 2 To lastRow

'Read the specific data from the worksheet.
With sht
drawing = .Cells(counter, 2)
weld = .Cells(counter, 3)
welder = .Cells(counter, 4)
pipeSize = .Cells(counter, 5)
weldingDate = .Cells(counter, 7)
pipeMaterial = .Cells(counter, 10)
End With

'Write the selected data into the text file.
Write #fNumber, drawing, weld, welder, pipeSize, weldingDate, pipeMaterial

'Continue looping until the last row.
Next counter

'Close the file.
Close #fNumber

'Inform the user about the process.
MsgBox "The specific data from the sheet '" & sht.Name & "' were successfully written to the '" & fName & "' file!", vbInformation

End Sub

VBA code for reading a text file using the Input statement:

Sub ReadTextFile()

'---------------------------------------------------------------------------------------------
'With ReadTextFile macro you can read data from a text file and import them into a worksheet.
'It is the "opposite" of WriteTextFile macro.

'Written By: Christos Samaras
'Date: 17/06/2012
'Last Updated: 16/03/2018
'E-mail: [email protected]
'Site: https://myengineeringworld.net/////
'---------------------------------------------------------------------------------------------

'Declaring the necessary variables.
Dim sht As Worksheet
Dim fName As String
Dim fNumber As Integer
Dim row As Long

'The next variables are related to the specific data that exist in the worksheet.
Dim drawing As String
Dim weld As String
Dim welder As String
Dim pipeSize As Integer
Dim weldingDate As Date
Dim pipeMaterial As String

'The full path of the text file that will be opened.
fName = ThisWorkbook.Path & "Excel Data (Write).txt"

'Set the sheet that will contain the imported data.
Set sht = ThisWorkbook.Sheets("Imported Data")

'Get an unused file number.
fNumber = FreeFile

On Error Resume Next

'Prepare the text file for reading.
Open fName For Input As #fNumber

'Check if the text file was found.
If Err.Number <> 0 Then
MsgBox "The text file was not found!", vbCritical, "Error!"
Exit Sub
End If

On Error GoTo 0

'Just showing the active sheet. It is not necessary.
sht.Activate

'Clear the sheet.
sht.Cells.Clear

'First row for data.
row = 1

'Loop until the end of file.
Do While Not EOF(fNumber)

'Read data from the text file.
Input #fNumber, drawing, weld, welder, pipeSize, weldingDate, pipeMaterial

'Write selected data to the worksheet.
With sht
.Cells(row, 1) = drawing
.Cells(row, 2) = weld
.Cells(row, 3) = welder
.Cells(row, 4) = pipeSize
.Cells(row, 5) = weldingDate
.Cells(row, 6) = pipeMaterial
End With

'Go to the next row of the worksheet.
row = row + 1

Loop

'Close the file.
Close #fNumber

'Fit the columns width.
sht.Cells.EntireColumn.AutoFit

'Inform the user about the process.
MsgBox "The specific data from the file '" & fName & "' were successfully imported into the sheet '" & sht.Name & "'!", vbInformation

End Sub

VBA code for writing to a text file using the Print statement:

Sub PrintAsString()

'-----------------------------------------------------------------------------------------------
'With PrintAsString macro you can write data into a text file using any string format you like.
'It is suitable for printing purposes. Here the "tab character" is used as a delimiter.

'Written By: Christos Samaras
'Date: 17/06/2012
'Last Updated: 16/03/2018
'E-mail: [email protected]
'Site: https://myengineeringworld.net/////
'-----------------------------------------------------------------------------------------------

'Declaring the necessary variables.
Dim sht As Worksheet
Dim line As String
Dim fName As String
Dim fNumber As Integer
Dim counter As Long
Dim lastRow As Long

'Set the sheet containing the data.
Set sht = ThisWorkbook.Sheets("Original Data")

'Find the last row that contains data.
With sht
.Activate 'Just showing the active sheet. It is not necessary.
lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
End With

'Set the name and the path of text file that will be created based on the workbook path.
fName = ThisWorkbook.Path & "Excel Data (Print).txt"

'Get an unused file number.
fNumber = FreeFile

'Create a new file (or overwrite an existing one).
Open fName For Output As #fNumber

'Loop through each row. If you want to skip headers change the counter = 1 to counter = 2.
For counter = 1 To lastRow

'Read specific data from the worksheet using Tab as the delimited character.
With sht
line = .Cells(counter, 2) & vbTab
line = line & .Cells(counter, 3) & vbTab
line = line & .Cells(counter, 4) & vbTab
line = line & .Cells(counter, 5) & vbTab
'The date format is a special case).
line = line & Format(.Cells(counter, 7), "dd-mm-yyyy") & vbTab
line = line & .Cells(counter, 10)
End With

'Write the data into the file.
Print #fNumber, line

'Continue looping until the last row.
Next counter

'Close the file.
Close #fNumber

'Inform the user about the process.
MsgBox "The values from the sheet '" & sht.Name & "' were successfully written to the '" & fName & "' file!", vbInformation

End Sub

VBA code for reading a text file using the Line Input statement:

Sub ReadStringData()

'-----------------------------------------------------------------------------------------------------
'With ReadStringData macro you can read each line of a text file as a single "string", then split the
'string in order to get the individual values and, finally, import these values into the worksheet.
'It is the "opposite" of PrintAsString macro.

'Written By: Christos Samaras
'Date: 17/06/2012
'Last Updated: 16/03/2018
'E-mail: [email protected]
'Site: https://myengineeringworld.net/////
'-----------------------------------------------------------------------------------------------------

'Declaring the necessary variables.
Dim sht As Worksheet
Dim line As String
Dim fName As String
Dim fNumber As Integer
Dim row As Long
Dim column As Integer
Dim dataValues As Variant
Dim counter As Integer

'The full path of the text file that will be opened.
fName = ThisWorkbook.Path & "Excel Data (Print).txt"

'Set the sheet that will contain the imported data.
Set sht = ThisWorkbook.Sheets("Imported Data")

'Get an unused file number.
fNumber = FreeFile

On Error Resume Next

'Prepare the text file for reading.
Open fName For Input As #fNumber

'Check if the text file was found.
If Err.Number <> 0 Then
MsgBox "The text file was not found!", vbCritical, "Error!"
Exit Sub
End If

On Error GoTo 0

'Just showing the active sheet. It is not necessary.
sht.Activate

'Clear the sheet.
sht.Cells.Clear

'First row for data.
row = 1

'Loop until the end of file.
Do While Not EOF(fNumber)

'Read the line data from the file.
Line Input #fNumber, line

'Split the single string into an array.
dataValues = Split(line, vbTab)

'Write the data into the sheet.
With sht

'First column for data.
column = 1

'Process each value of the array.
For counter = LBound(dataValues) To UBound(dataValues)

'Write the value into the worksheet.
.Cells(row, column) = dataValues(counter)

'Increase column count.
column = column + 1

Next counter

End With

'Go to the next row of the worksheet.
row = row + 1

Loop

'Close the file.
Close #fNumber

'Fit the columns width.
sht.Cells.EntireColumn.AutoFit

'Inform the user about the process.
MsgBox "The data from the file '" & fName & "' were successfully imported into the sheet '" & sht.Name & "'!", vbInformation

End Sub

Demonstration video


This short video demonstrates the four subs in action.


Downloads


Download
This sample workbook contains the VBA code described above. In the first sheet there are four buttons that correspond to the four procedures (Write, Input, Print, Line Input). In the second sheet, there are some data that are exported to a text file. Finally, the third sheet is used to import data from a text file. The workbook can be opened with Excel 2007 or newer. Please enable macros before using it.

Page last modified: January 6, 2019

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

  • Which link is down?
    I check the workbook download link, and it is fine.
    If something is wrong, please tell me in order to fix it.

    Thank you in advance!
    Christos

  • Hi SamT,

    Thank you very much for the functions.
    It is always nice to see how an "old" code can be used to create something new. Thank you also for the reference in your functions.

    Keep coding…

    Kind Regards,
    Christos

  • Function WriteLinesToTextFile(FileFullName As String, LinesToWrite As Collection) As Boolean
    'By SamT: "samtyler" & X & "mail" & Y & "com" _
    where X = "@" and Y = "."
    'With thanks to Christos Samaras: https://myengineeringworld.net//////

    'FileFullName must include the full Path and name of the File.
    'If the file exists, it will be overwritten.
    'If the Function succeeds, it will return "True" _
    ElseIf it fails for any reason, it will return "False.

    ''''Set Error Condition
    On Error GoTo ReportError

    ''''Declare Variables
    Dim FileName As String 'I prefer to use internal variables
    Let FileName = FileFullName 'I prefer to "Let" Var = Value in Declarations
    Dim TxtFileNum As Integer
    Dim TheLines As Collection
    Set TheLines = LinesToWrite 'Using internal variable

    ''''Get an unused file number
    TxtFileNum = FreeFile

    ''''Create a new file (or overwrite an existing one)
    Open FileName For Output As #TxtFileNum

    '''Loop thru the collection and write each Item to the Text file
    Dim i As Long
    For i = 1 To TheLines.Count
    'Write data to file
    Print #TxtFileNum, TheLines(i)
    Next i

    ''''Close the file
    Close #TxtFileNum

    ''''Success, Set Return to "True" and exit
    WriteLinesToTextFile = vbTrue
    Exit Function

    ''''Failure, Set Return to "False" before Ending.
    ReportError:
    WriteLinesToTextFile = vbFalse
    End Function

  • SamT, again.

    I tutor VBA on one of the many VBA forums, and we have a guest who needs to perform a complicated parse and replace or insert lines.

    I rewrote two of your procedures as universal functions using collections to pass the line Strings back and forth.

    I thought you might be interested in seeing a different way of doing things.

    I have to break the functions across two comments.

    Function LinesFromTextFile(FileFullName As String) As Collection
    'By SamT: "samtyler" & X & "mail" & Y & "com" _
    where X = "@" and Y = "."
    'With thanks to Christos Samaras: https://myengineeringworld.net//////

    'FileFullName must include the full Path and name of the File.
    'Returns a Collection of text lines from the file where _
    Collection.Index = Line Number.
    'If an Error occurs while reading the text file, first four characters _
    of Collection.Item(1) will be "Err:"

    ''''Declare variables
    Dim LineString As String
    Dim FileName As String 'I prefer to use internal variables
    Let FileName = FileFullName 'I prefer to "Let" Var = Value in Declarations
    Dim TxtFileNum As Integer
    Dim Result As New Collection

    ''''Get an unused file number
    TxtFileNum = FreeFile

    ''''Set an Error condition
    On Error Resume Next

    ''''Open the File for reading
    Open FileName For Input As #TxtFileNum

    ''''Check if the text file was found
    If Err.Number <> 0 Then
    MsgBox "File not found!", vbCritical, "Error!"
    Exit Function
    End If

    ''''Reset the Error Condition
    On Error GoTo ErrorReport 'Jump to Line Label

    ''''Read the File, line by line, until the end of file
    Do While Not EOF(TxtFileNum)
    Line Input #TxtFileNum, LineString
    'Add the line to a Collection
    Result.Add LineString
    Loop

    ''''Close the file
    Close #TxtFileNum

    ''''Set the Function's Return value
    Set LinesFromTextFile = Result

    ''''Success! Exit Function without reporting an error
    Exit Function

    ''''Oops! an Error occured while reading the file.
    ErrorReport:
    LinesFromTextFile.Add "Err: Something bad happened while reading the text file!"
    End Function

  • Thanks Christos, that works nicely. I think I misread your earlier statement, which is why I wasn't sure which one to use.

    Thanks again!

    James

  • Hi James,

    You should use the Print macro.

    As I wrote in the beginning of this post, “The output of Write statement is a line in which the data values are separated by commas, while hash marks (#) are around dates and quotes (“) around strings.”

    So, since the quotes are automatically inserted due to Write statement, it would be easier to use the Print statement.

    Warm Regards,
    Christos

  • Hi Christos,

    I'm using the writeTextFile code but it's including the quotation marks for each cell. Do I have to use the Print as string code instead to exclude the quotations or is there another modification I can use? It works really well apart from this.

    Thanks,

    James

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Add Content Block
    >