How to use Excel VBA to write text files as output

This is a post on a recent trial that I have in terms of converting a set of excel data organized as a list into an output text file. Sometimes this may be need to be done in order to enable migration of excel data into a new system that only reads text files.

Software Lab Excel To CSV
Description:
This set of VBA codes can be used to convert an Excel data Table/List into CSV file. The CSV file format is very useful as it can be used as a platform for migrating data from Excel into various database environments. The flexibility behind this codes is that VBA can be used to manipulate the dataset so that the format can be triggered and maintained with minimal user intervention. This will facilitate the optimization of the CSV file for import packages.
Code:

Sub PrintToTextFile()
    
    
    Dim ws As Worksheet
    Dim FName As String
    Dim FNumber As Integer
    Dim LastRow As Long
    Dim i As Long
    Dim Map As Long
    Dim TextLine As String
    Dim HeaderLine As String
    
    ' Set your worksheet to where the data resides
    Set ws = Sheet6
    ws.Activate
    Range("A1").Select
    
    'Find the last row that contains data
    With ws
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    'Naming your text file
    HeaderLine = ws.Range("H1").Value
    FName = ThisWorkbook.Path & "\" & HeaderLine & "_QboImport.csv"
    
    'Get an unused file number
    FNumber = FreeFile
    
    Open FName For Output As #FNumber
    
    For i = 3 To LastRow
    
        With ws
            
            TextLine = Format(.Cells(i, 1), "dd-mm-yy") & ","
            TextLine = TextLine & .Cells(i, 2) & " | " & .Cells(i, 4) & ","    'Looks at Column 2 & 4 and merges them
            TextLine = TextLine & .Cells(i, 3) * -1  'Looks at Column 3
                    
        End With
                
        Print #FNumber, TextLine
    
    Next i
    
    
    Close #FNumber
       
    MsgBox ("Your Output Text File can be found at " & FName)
    
       
End Sub
Code:
Option Explicit

Sub PrintToTextFile()
    
    
    Dim ws As Worksheet
    Dim FName As String
    Dim FNumber As Integer
    Dim LastRow As Long
    Dim i As Long
    Dim TextLine As String
    Dim HeaderLine As String
    
    ' Set your worksheet to where the data resides
    Set ws = Sheet4
    ws.Activate
    Range("A1").Select
    
    'Find the last row that contains data
    With ws
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    'Naming your text file
    FName = ThisWorkbook.Path & "\TestPrint.txt"
    
    'Get an unused file number
    FNumber = FreeFile
    
    Open FName For Output As #FNumber
    
    HeaderLine = "!STARTTRN" & vbTab
    Print #FNumber, HeaderLine
    Print #FNumber, "Column1" & vbTab & "Column2"
    
    'Loop each line that contains data into the output text file
    For i = 2 To LastRow
    
        With ws
            
            TextLine = .Cells(i, 2) & vbTab     'Looks at Column 2
            TextLine = TextLine & .Cells(i, 3) & vbTab      'Looks at Column 3
            TextLine = TextLine & Format(.Cells(i, 1), "dd-mm-yyyy") & vbTab    'Looks at Column 1
        
        End With
                
        Print #FNumber, TextLine
    
    Next i
    
    HeaderLine = "!STARTTRN" & vbTab
    Print #FNumber, HeaderLine

    
    Close #FNumber
       
    MsgBox ("Your Output Text File can be found at " & FName)
    
       
End Sub

Procedures:
  1. Insert a new module (VBA) in your excel file.
  2. Copy and paste the above codes into the module.
  3. Based on codes above, do take note that your excel range data starts from row 3. Therefore row 2 will be your header columns if you have any.
  4. The above codes are based off a 4 column data as follows: DATE | DESCRIPTION | AMOUNT | DETAIL DESCRIPTION
  5. The codes can be modified to increase or decrease the number of columns and also its order.

Leave a Reply

Your email address will not be published. Required fields are marked *