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?)
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
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
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
Set oSh = ThisWorkbook.Worksheets("worksheet")
Where “worksheet” is the name of the worksheet the data is on.
- Database Administration Tutorials
- Programming Tutorials & IT News
- Linux & DevOps World
- Ebook Reviews
- PES Matches, Skills & News