This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
XL can output files in a variety of plain text forms. CSV, or comma separated value, format outputs each cell in a row with a comma separating them, and each row has a line terminator (such as a carriage return, line feed or both) after each row. In order to preserve data, any cell with a comma as part of the text (as opposed to the formatting: a currency formatted cell displaying $4,321 will output as 4321) has a pair of quotation marks applied, so that the two cells
are output as
text,"hello, goodbye"
This way, the comma is not interpreted as a separator when the file is read back in. This of course, means that a cell containing
needs to have some indication that the quotation marks are part of the text, not the delimiter. XL will convert quotation marks (") to double quotation marks ("") and then surround the entire cell's contents with quotation marks:
"""this - "" - is a quotation mark"""
Other text formats, such as space delimited or tab delimited are handled similarly, and XL will read them back in without problem.
Occasionally, however, other applications require text files in a different format: different delimiter, no quotation marks, quotation marks around all values, etc. In those cases you can use macros to write out a text file in any form you'd like. Note that in these cases, you'll need to use the Text Wizard to tell XL how to import the data.
This macro will output a file with whatever delimiter is specified in the constant DELIMITER (in this case, the pipe symbol). Note that it can be more than one character:
Public Sub CharacterSV()
Const DELIMITER As String = "|"
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String
nFileNum = FreeFile
Open "Test.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells, _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub
This macro surrounds each field with quotation marks, doubling any quotation marks found in the cell text:
Public Sub OutputQuotedCSV()
Const QSTR As String = """"
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String
nFileNum = FreeFile
Open "File1.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & "," &QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub
This macro will output a text file without surrounding cells which have commas in quotation marks, or doubling quotation marks in the text:
Public Sub TextNoModification()
Const DELIMITER As String = "," 'or "|", vbTab, etc.
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String
nFileNum = FreeFile
Open "Test.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub
Sometimes applications require input files with fields of a fixed width. For instance, each record may consist of one line, with field one, consisting of 20 characters, starting at character 1, and field 2, consisting of 10 characters, starting at character 21, etc., The field widths are constant, regardless of how many characters of data are in the field. Fields with fewer than the requisite number of characters must be padded with spaces or other characters. This macro will produce a text file with fixed fields. The field widths are contained in vFieldArray. There is normally no delimiter, but the code allows for one.
Public Sub FixedFieldTextFile()
Const DELIMITER As String = "" 'Normally none
Const PAD As String = " " 'or other character
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String
'vFieldArray contains field lengths, in characters, from field 1 to N
vFieldArray = Array(20, 10, 15, 4)
nFileNum = FreeFile
Open "Test.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))
Next i
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
