Save Each Row of Spreadsheet as Seperate Text File?

  • Thread starter Thread starter parkins
  • Start date Start date
P

parkins

All,

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

Thanks.

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

Patrick
 
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!
 
Back
Top