Introduction
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.
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.
Christos dude.. your primary link is down.
Please fix it.
Thanks in advance.
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
Thank you very much SamT!
I am just trying to include as much comments as possible in order to be easily understood…
Warm Regards,
Christos
I love your coding style; Very easy to understand.
Thank you,
SamT
Hi James,
Since your problem was solved, no problem at all.
Kind Regards,
Christos
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