VBA Write to Text File

VBA Write to Text File using two statements. The first statement is Write or the second statement is Print. The Write statement generates a line of values and it is separated by commas, and adds hash marks(#) around dates and quotes(“) around strings. The Print statement generates a line which is suitable to be printed, with the data is arranged in columns with spaces between. Let us learn syntax and examples on Write and print statements.

Table of Contents:

Objective
Syntax for Write to Text File
Writing to Text File using Write Statement
Writing to Text File using Print Statement
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax for Write to Text File

You can find following syntax for Write to Text File in Excel VBA.
Syntax:

Write #FileNumber, Input_Value1, Input_Value2, ...
'or
Print #FileNumber, Line_Input_String

Where FileNumber represents the unique number to file and contains numeric value. It helps to open text files.
Input_Value1, Input_Value2, … represents the input data which you want to write to text file. All values are separated by commas, and it adds hash marks(#) around dates and quotes(“) around strings while writing to text file.
and Line_Input_String represents the input string to write to text file.

Writing to Text File using Write Statement

You can find following VBA Code. It helps to write to text files using Write statement in Excel.

'Writing to Text File using Write Statement
Sub VBAF1_Write_To_Text_File_Using_Write_Statement()
    
    'Variable Declaration
    Dim sFilePath As String
    Dim iStRow As Integer, iLstRow As Integer
    Dim dDate As Date, sCountry As String, sRegion As String, iUnits As Integer
   
   'Specify Text File Path
    sFilePath = "C:\VBAF1\FirstTextFile.txt"
        
    'Get Unique File Number using FreeFile
    fileNumber = FreeFile
    
    'Check Specified Text File exists or not
    If VBA.Dir(sFilePath) = "" Then MsgBox "File Does not exists": End
    
    'Open TextFile in Output mode
    Open sFilePath For Output As #fileNumber
    
    'Find Last Row
    iLstRow = Sheets("Input Data").Cells.SpecialCells(xlCellTypeLastCell).Row
        
    'Loop through all rows in Worksheet
    For iStRow = 2 To iLstRow
        'Write Data to Text File
        With Sheets("Input Data")
            dDate = .Cells(iStRow, 1).Value
            sCountry = .Cells(iStRow, 2).Value
            sRegion = .Cells(iStRow, 3).Value
            iUnits = .Cells(iStRow, 4).Value
        End With
    
        'Write Data to Text File
        Write #fileNumber, dDate, sCountry, sRegion, iUnits
       
    Next
    
    'Close Text File
    Close #fileNumber
        
End Sub

Output: You can find following Input and output screenshot for your reference.

VBA Write to Text File using Write Statement

Writing to Text File using Print Statement

You can find following VBA macro. It helps to write to text files using Print statement in Excel.

'Writing to Text File using Print Statement
Sub VBAF1_Write_To_Text_File_Using_Print_Statement()
    
    'Variable Declaration
    Dim sFilePath As String
    Dim iStRow As Integer, iLstRow As Integer
    Dim sDataLine As String
   
   'Specify Text File Path
    sFilePath = "C:\VBAF1\FirstTextFile.txt"
        
    'Get Unique File Number using FreeFile
    fileNumber = FreeFile
    
    'Check Specified Text File exists or not
    If VBA.Dir(sFilePath) = "" Then MsgBox "File Does not exists": End
    
    'Open TextFile in Output mode
    Open sFilePath For Output As #fileNumber
    
    'Find Last Row
    iLstRow = Sheets("Input Data").Cells.SpecialCells(xlCellTypeLastCell).Row
        
    'Loop through all rows in Worksheet
    For iStRow = 2 To iLstRow
        'Write Data to Text File
        With Sheets("Input Data")
            sDataLine = Format(.Cells(iStRow, 1).Value, "dd-mmm-yyyy") & ";"
            sDataLine = sDataLine & .Cells(iStRow, 2).Value & ";"
            sDataLine = sDataLine & .Cells(iStRow, 3).Value & ";"
            sDataLine = sDataLine & .Cells(iStRow, 4).Value
        End With
    
        'Write Data to Text File
        Print #fileNumber, sDataLine
       
    Next
    
    'Close Text File
    Close #fileNumber
        
End Sub

Output: You can find following Input and output screenshot for your reference.