VBA Write Statement

Write Description

Writes data to a sequential file.

Write Syntax

Write #FileNumber, [OutputList]

The Write statement contains 2 arguments:

FileNumber: Any valid file number.

OutputList: Optional. One or more comma-delimited numeric expressions or string expressions to write to a file.

Examples of Excel VBA Write Function

First Name	Last Name	Age	Gender
Robert	Stepp	20	Male
Jennifer	Mariscal	33	Female
David	Romig	35	Male
Carmel	Ingram	26	Female

To output Range(“A2:D5”) in a sheet like the above picture to a file, you can use the following code.

Sub WriteRangeToFile(strFile As String, rng As Range)
    Dim row As Range, cell As Range
    Dim FileNumber As Integer
    FileNumber = FreeFile
    
    Open strFile For Output As #FileNumber
        For Each row In rng.Rows
            For Each cell In row.Cells
                If cell.Column = row.Cells.Count Then
                    Write #FileNumber, cell
                Else
                    Write #FileNumber, cell,
                End If
            Next cell
        Next row
    Close #FileNumber
End Sub

Sub Write_Example()
    Dim strFolder As String
    Dim strFile As String
    Dim dlgFolder As FileDialog
    Dim rng As Range
    
    Set dlgFolder = Application.FileDialog(msoFileDialogFolderPicker)
    
    If dlgFolder.Show = True Then
        strFolder = dlgFolder.SelectedItems(1)
    Else
        Exit Sub
    End If
    
    Set rng = Range("A1:D5")
    
    strFile = "Write_Output.txt"
    WriteRangeToFile strFolder & "\" & strFile, rng
End Sub

The output to the file “Write_Output.txt”:

"First Name","Last Name","Age","Gender"
"Robert","Stepp","20","Male"
"Jennifer","Mariscal","33","Female"
"David","Romig","35","Male"
"Carmel","Ingram","26","Female"