Sunday, 17 June 2012



Excel data

Figure 1: Excel data.

Text data

Figure 2: Text data.

The integration between excel and text files is still an important issue. The input and output of many dated programs is 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 will help you automating the procedure.


How to do it

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 with 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 as 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 text file using Write statement:

Sub WriteTextFile()
    
    'Write data to a text file
    'By Christos Samaras
    
    'Declaring variables
    Dim sDrawing As String
    Dim sWeld As String
    Dim sWelder As String
    Dim intSize As Integer
    Dim dDate As Date
    Dim sMaterial As String
    
    Dim sFName As String
    Dim intFNumber As Integer
    Dim lCounter As Long
    Dim lLastRow As Long
    
    'Just showing where the input data are
    Sheet2.Activate
    Range("A1").Select
    
    'Find the last row that contains data
    With Sheet2
        lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    'Setting the name and the path of text file based on workbook path
    sFName = ThisWorkbook.Path & "\Excel Data (Write).txt"
        
    'Get an unused file number
    intFNumber = FreeFile
    
    'Create a new file (or overwrite an existing one)
    Open sFName For Output As #intFNumber
        
    For lCounter = 2 To lLastRow 

     'Read specific data from the worksheet        
        With Sheet2
            sDrawing = .Cells(lCounter, 2)
            sWeld = .Cells(lCounter, 3)
            sWelder = .Cells(lCounter, 4)
            intSize = .Cells(lCounter, 5)
            dDate = .Cells(lCounter, 7)
            sMaterial = .Cells(lCounter, 10)
        End With
        
        'Write selected data to text file
        Write #intFNumber, sDrawing, sWeld, sWelder, intSize, dDate, sMaterial
        
    'Continue looping until the last row
    Next lCounter
    
    'Close the text file
    Close #intFNumber
    
    'Inform the user about the process
    MsgBox "Values from sheet '" & Sheet2.Name & "' were written to '" & sFName & "' file!", vbInformation
   
End Sub 


VBA code for reading a text file using Input statement:

Sub ReadTextFile()

    'Read data from a text file
    'By Christos Samaras
    
    'Declaring variables
    Dim sDrawing As String
    Dim sWeld As String
    Dim sWelder As String
    Dim intSize As Integer
    Dim dDate As Date
    Dim sMaterial As String
    
    Dim sFName As String
    Dim intFNumber As Integer
    Dim lRow As Long
    
    'The full path of the text file that will be opened
    sFName = ThisWorkbook.Path & "\Excel Data (Write).txt"
    
    'Get an unused file number
    intFNumber = FreeFile
    
    On Error Resume Next
    
    'Prepare text file for reading
    Open sFName For Input As #intFNumber
    
    'Check if the text file was found
    If Err.Number <> 0 Then
        MsgBox "Text file not found!", vbCritical, "Error!"
        Exit Sub
    End If
    
    On Error GoTo 0

    
    'Clearing the sheet
    Sheet3.Cells.Clear
    
    lRow = 1
    
    'Loop until the end of file
    Do While Not EOF(intFNumber)
        
        'Read data from the text file
        Input #intFNumber, sDrawing, sWeld, sWelder, intSize, dDate, sMaterial

        'Write selected data to the worksheet
        With Sheet3
            .Cells(lRow, 1) = sDrawing
            .Cells(lRow, 2) = sWeld
            .Cells(lRow, 3) = sWelder
            .Cells(lRow, 4) = intSize
            .Cells(lRow, 5) = dDate
            .Cells(lRow, 6) = sMaterial
        End With

        'Address next row of worksheet
        lRow = lRow + 1

    Loop
      
    'Close the text file
    Close #intFNumber
        
    'Fitting column width
    Sheet3.Cells.EntireColumn.AutoFit

    'Just showing where the output data are
    Sheet3.Activate
    Range("A1").Select
    
    'Inform the user about the process
    MsgBox "Values from file '" & sFName & "' were imported to sheet '" & Sheet3.Name & "'!", vbInformation

End Sub 


VBA code for writing to text file using Print statement:

Sub PrintAsString()
    
    'With print command you can write text files in any format
    'Here tab is used as delimited character
    'By Christos Samaras
    
    'Declaring variables
    Dim sLine As String
    Dim sFName As String
    Dim intFNumber As Integer
    Dim lCounter As Long
    Dim lLastRow As Long
        
    'Just showing where the input data are
    Sheet2.Activate
    Range("A1").Select
    
    'Find the last row that contains data
    With Sheet2
        lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    'Setting the name and the path of text file based on workbook path
    sFName = ThisWorkbook.Path & "\Excel Data (Print).txt"
    
    'Get an unused file number
    intFNumber = FreeFile
    
    'Create a new file (or overwrite an existing one)
    Open sFName For Output As #intFNumber
    
    For lCounter = 2 To lLastRow

    'Read specific data from the worksheet        
        With Sheet2
            'Using tab as delimited character
            sLine = .Cells(lCounter, 2) & vbTab
            sLine = sLine & .Cells(lCounter, 3) & vbTab
            sLine = sLine & .Cells(lCounter, 4) & vbTab
            sLine = sLine & .Cells(lCounter, 5) & vbTab
            'Determine the date format
            sLine = sLine & Format(.Cells(lCounter, 7), "dd-mm-yyyy") & vbTab
            sLine = sLine & .Cells(lCounter, 10)
        End With
                
        'Write data to file
        Print #intFNumber, sLine
    
    'Continue looping until the last row
    Next lCounter
    
    'Close the file
    Close #intFNumber
       
    'Inform the user about the process
    MsgBox "Values from sheet '" & Sheet2.Name & "' were written to '" & sFName & "' file!", vbInformation
       
End Sub


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

Sub ReadStringData()
    
    'To read "string" data, the string must be split in order to locate the values
    'By Christos Samaras
    
    'Declaring variables
    Dim sLine As String
    Dim sFName As String
    Dim intFNumber As Integer
    Dim lRow As Long
    Dim lColumn As Long
    Dim vDataValues As Variant
    Dim intCount As Integer
    
    'The full path of the text file that will be opened
    sFName = ThisWorkbook.Path & "\Excel Data (Print).txt"
    
    'Get an unused file number
    intFNumber = FreeFile
    
    On Error Resume Next
    
    'Prepare text file for reading
    Open sFName For Input As #intFNumber
    
    'Check if the text file was found
    If Err.Number <> 0 Then
        MsgBox "Text file not found!", vbCritical, "Error!"
        Exit Sub
    End If
    
    On Error GoTo 0
    
    'Clearing the sheet
    Sheet3.Cells.Clear
    
    'First row for data
    lRow = 1
    
    'Loop until the end of file
    Do While Not EOF(intFNumber)
        
        'Read data from file
        Line Input #intFNumber, sLine
        
        'Split values apart into an array
        vDataValues = Split(sLine, vbTab)
        
        With Sheet3
            
            'First column for data
            lColumn = 1
            
            'Process each value in the array
            For intCount = LBound(vDataValues) To UBound(vDataValues)
            
                'Write the value to the worksheet
                .Cells(lRow, lColumn) = vDataValues(intCount)
                
                'Increase column count
                lColumn = lColumn + 1
            Next intCount
            
        End With
        
        'Next row of the worksheet
        lRow = lRow + 1
               
    Loop
    
    'Close the file
    Close #intFNumber
    
    'Fitting column width
    Sheet3.Cells.EntireColumn.AutoFit

    'Just showing where the output data are
    Sheet3.Activate
    Range("A1").Select
    
    'Inform the user about the process
    MsgBox "Values from file '" & sFName & "' were imported to sheet '" & Sheet3.Name & "'!", vbInformation

End Sub


Video

This short video demonstrates the four subs in action.



Download it from here


This sample file 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 file can be opened with Office 2007 or newer. Please, remember to enable macros before using the workbook.

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: tel. +30-6973513308, e-mail , Facebook , Twitter , Google+ and Linkedin . Full CV