|< Day Day Up >|| |
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:
Open or create the file using the Open statement.
Specify the position in the file using the Seek function, which is optional.
Write the data to the file using the Write # or the Print # statement.
Close the file using the Close statement.
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.
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 _
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.
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.
Dim FileName As String
Dim NumRows As Long
Dim NumCols As Integer
Dim r As Long
Dim c As Integer
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;
Write #1, Data
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.
|< Day Day Up >|| |