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

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. As with the write procedure there are also two ways of reading data from a text file: you can either useorstatement. 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: xristos.samaras@gmail.com

'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 forto a text file using thestatement:

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: xristos.samaras@gmail.com

'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: xristos.samaras@gmail.com

'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: xristos.samaras@gmail.com

'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

Downloads

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.

This short video demonstrates the four subs in action.