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


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
End Sub

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


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)
            End If

    Set myFileSystemObject = Nothing
    Set fileOut = Nothing
End Sub
  • 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 '12 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 – Raystafarian Feb 7 '12 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. – Raystafarian Feb 8 '12 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.