Save Each Row of Spreadsheet as Seperate Text File?




I have a 200-row text file laid out as follows:

| Column 1 | Column 2 |
| target1.txt | my string 1|
| target2.txt | my string 2|
| target3.txt | my string 3|
| target4.txt | my string 4|
| target200.txt | my string 200|

Anyone know a way to set up a macro in Excel that will save each | my
string n| as a text file named according to the value in Column 1?

And for bonus points, In an ideal world, the text file created will
also have one other line in it before the |string|, which will remain
constant across all 200 files. If I have to insert that constant text
into the Excel file and write two rows from Excel, I'm prepared to do
that if someone can suggest a way to write one!.



Dave Peterson

Make sure that the folder you want to put the files in already exists:

Option Explicit
Sub CreateFiles()

Dim wks As Worksheet
Dim iRow As Long
Dim myHeader As String
Dim myFolderName As String
Dim FileNum As Long

myFolderName = "C:\temp\"

Set wks = Worksheets("sheet1")

myHeader = "Some Header Information here"

FileNum = FreeFile
With wks
For iRow = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row
Close #FileNum
Open myFolderName & .Cells(iRow, "A").Value For Output As FileNum
Print #FileNum, myHeader
Print #FileNum, .Cells(iRow, "B").Value
Next iRow
End With

Close #FileNum

MsgBox "Done."

End Sub


Dave -

Works like a charm - my eyes and fingers thank you.

OK if I post your code on one of the Adobe forums - it's part of an
issue I was struggling with to update PDFs.

Aug 1, 2019
Reaction score
Finally I found a code that does what I wanted to do with my data! Just a little question though:

Let's pretend that I have an additional "row C" whose content I wish to add to myheader.

So instead of having

myHeader = "Some Header Information here"

I would like to have

myHeader = "the content of row C"

What exactly do I need to write after myHeader = in order to have the content of row C as a header?

Thanks in advance!

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question