copy

Sunday, 17 June 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:        xristos.samaras@gmail.com
    'Site:          https://www.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:        xristos.samaras@gmail.com
    'Site:          https://www.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://www.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://www.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.

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info