Page 322 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

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.

Chapter 13
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
Forc=1ToNumCols
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.

Reading from an External File

The procedure to read a file is quite similar to the procedure used to write to a file. The steps
required to read a text file using VBA are listed here:
1 Open the file using the Open statement.
2 Specify the position in the file using the Seek function, which is optional.

296
Part 4: Advanced VBA