Outputting Excel rows to a series of text files

Inside Excel, I have a list of article names in column A, and a disclaimer inside column B. Now for each article in column A, I would like to create a text file, were A is the title of the file and B, the disclaimer, is the contents of the file.

Is this possible?

The idea is that I have several hundred of these, and I would like to make this easier on myself.

If Excel is not ideal for this, can anyone suggest an alternative? (possibly Notepad++ has a feature that can help?)

Solution 1:

Sub Export_Files()
    Dim sExportFolder, sFN
    Dim rArticleName As Range
    Dim rDisclaimer As Range
    Dim oSh As Worksheet
    Dim oFS As Object
    Dim oTxt As Object

    'sExportFolder = path to the folder you want to export to
    'oSh = The sheet where your data is stored
    sExportFolder = "C:\Disclaimers"
    Set oSh = Sheet1

    Set oFS = CreateObject("Scripting.Filesystemobject")

    For Each rArticleName In oSh.UsedRange.Columns("A").Cells
        Set rDisclaimer = rArticleName.Offset(, 1)

        'Add .txt to the article name as a file name
        sFN = rArticleName.Value & ".txt"
        Set oTxt = oFS.OpenTextFile(sExportFolder & "\" & sFN, 2, True)
        oTxt.Write rDisclaimer.Value
        oTxt.Close
    Next
End Sub

Solution 2:

Another approach using variant arrays for speed, and an alternative to the FileSystemObject given only file creation is needed.

    Sub DataDump()

    Dim X
    Dim lngRow As Long
    Dim StrFolder As String

    StrFolder = "C:\temp"
    X = Range([a1], Cells(Rows.Count, 2).End(xlUp))
    For lngRow = 1 To UBound(X)
    Open StrFolder & "\" & X(lngRow, 1) & ".txt" For Output As #1
    Write #1, X(lngRow, 2)
    Close #1
    Next
    End Sub

Solution 3:

I do not have enough Rep to comment on other people's post yet so I have to post this as an answer. The accepted answer by @transistor1 works; however, in Excel 2010 need to change

Set oSh = Sheet1

to Set oSh = ThisWorkbook.Worksheets("worksheet")

Where "worksheet" is the name of the worksheet the data is on.