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
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.
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