Writing to an External File

---

VBA contains a number of statements that allow file manipulation. These Input/Output statements give you more control over files than Excel’s normal text file import and export options.

The standard procedure for writing to a text file is listed here:

1. Open or create the file using the Open statement.

2. Specify the position in the file using the Seek function, which is optional.

3. Write the data to the file using the Write # or the Print # statement.

4. Close the file using the Close statement.

   Note 

   Do not confuse the VBA Open statement with the Open method for the Application object. VBA’s Open statement is used to open a file for reading or writing whereas the Open method for the Application object actually opens the file.

Inside Out
Opening a Text File

Before you are able to read or write to a file, you must open it. The Open statement is quite versatile, and the syntax can be a challenge.

Open pathname For mode [Access access] [lock] As [#]filenumber _
[Len=reclength]

• pathname A required element that contains the name and path of the file to be opened.

• mode A required element that specifies which mode the file will be using, such as Append, Input, Output, Binary, or Random.

  Note 

  The VBA Help file for the mode parameter says that it’s required but that if you leave it out, Excel will assume the mode is Random. We weren’t able to resolve the contradiction, so the authors’ advice is to always set the parameter.

• access Specifies the file operation as Read, Write, or Read Write.

• lock Specifies the file status as Shared, Lock Read, Lock Write, or Lock Read Write.

• filenumber A required element that sets the file number ranging from 1 to 511. The FreeFile function can be used to assign the next available number.

• reclength Sets the record length for random access files or the buffer size for sequential access files.

The following example exports data from a specified range to a CSV text file. Notice that the procedure uses two Write # statements. The first statement ends with a semicolon, so a carriage return/linefeed sequence is not written. For the last cell in a row, however, the second Write # statement does not use a semicolon, which causes the next output to appear on a new line.

Sub ExportSelectedRange()
    Dim FileName As String
    Dim NumRows As Long
    Dim NumCols As Integer
    Dim r As Long
    Dim c As Integer
    Dim Data
    Dim ExpRng As Range
    
    Set ExpRng = Selection
    NumCols = ExpRng.Columns.Count
    NumRows = ExpRng.Rows.Count
    FileName = "C:\textfile.txt"
    Open FileName For Output As #1
        For r = 1 To NumRows
            For c = 1 To NumCols
                Data = ExpRng.Cells(r, c).Value
                If IsNumeric(Data) Then Data = Val(Data)
                If IsEmpty(ExpRng.Cells(r, c)) Then Data = ""
                If c <> NumCols Then
                    Write #1, Data;
                Else
                    Write #1, Data
                End If
            Next c
        Next r
    Close #1
End Sub

The variable named Data stores the contents of each cell. If the cell is numeric, the variable is converted to a value. This step ensures that numeric data will not be stored with quotation marks. If a cell is empty, its Value property returns 0. Therefore, the code also checks for a blank cell using the IsEmpty function and substitutes an empty string instead of a zero. It’s also important to remember that a date is actually a value that’s formatted to appear in a common date format. Remember that if the information in the variable Data contains a date, the value is what will actually be stored in this variable.

---