User Tools

Site Tools


interfaces:writing_text_files_with_no_modification_from_excel

Writing text files with no modification from Excel

Ref: http://www.mcgimpsey.com/excel/textfiles.html

Text files with no modification

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
interfaces/writing_text_files_with_no_modification_from_excel.txt · Last modified: 2009/01/23 12:17 by support