2

I'm looking to create a .txt file for each value in column A containing the corresponding values in columns B and C

0

2 Answers 2

1

The more I looked at this the more I found it to be a useful little macro. In order to keep it from processing blank rows (and locking up my Excel) I rewrote the code to only create a file while there is data available. Also, the use of print rather than write creates text without the quotations. Here is what I used to accomplish the same thing.

Sub CreateFile()
Do While Not IsEmpty(ActiveCell.Offset(0, 1))
    MyFile = ActiveCell.Value & ".txt"
    'set and open file for output
    fnum = FreeFile()
    Open MyFile For Output As fnum
    'use Print when you want the string without quotation marks
    Print #fnum, ActiveCell.Offset(0, 1) & " " & ActiveCell.Offset(0, 2)
Close #fnum
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Feel free to use and modify as you wish. Thanks for the great idea.

0

This macro will take each value in column A, produce a .txt document in the name of the value and insert the corresponding information from columns B and C

Sub CreateTxt()
'
For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlDown).Row)
    Open ce & ".txt" For Output As #1
    Write #1, ce.Offset(0, 1) & " " & ce.Offset(0, 2)
    Close #1
Next ce
End Sub

Four years later I decided to come back to my first SU question - my very beginnings to VBA. This is better, but will overwrite any file that already exists

Option Explicit

Sub CreateFileEachLine()

    Dim myPathTo As String
    myPathTo = "C:\Users\path\to\"
    Dim myFileSystemObject As Object
    Set myFileSystemObject = CreateObject("Scripting.FileSystemObject")
    Dim fileOut As Object
    Dim myFileName As String

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim i As Long

        For i = 1 To lastRow
            If Not IsEmpty(Cells(i, 1)) Then
                myFileName = myPathTo & Cells(i, 1) & ".txt"
                Set fileOut = myFileSystemObject.CreateTextFile(myFileName)
                fileOut.write Cells(i, 2) & "    " & Cells(i, 3)
                fileOut.Close
            End If
        Next

    Set myFileSystemObject = Nothing
    Set fileOut = Nothing
End Sub
3
  • Great idea. This code created the text files for the data I have in two rows, but then causes Excel to stop responding. The line For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlDown).Row) returns over a millions rows even though only two are populated.
    – CharlieRB
    Feb 7, 2012 at 21:33
  • That hasn't happened to me, but I think I select it and use a keyboard shortcut to run the macro. Ideally, I should put in my range, but recently it's been varying too much Feb 7, 2012 at 22:22
  • Additionally, you could have it start at the bottom and xlUp to the top to avoid there being blank sheets. I'm wide open to a more elegant solution. Feb 8, 2012 at 13:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .