Solved: output excel rows to txt files

[jramsey]

I have been searching around for a script / macro that will create a text file from each row of a work sheet and name it from the first cell of the row.
Each row has 9 cells and I want to create the file from cells B - I (each Cell on a new line of the text file) and name it from cell A. I have many sheets that are formatted the same way with hundreds of rows on them.

The output file should look like this

FILE NAME = Cell A.mtag

cell B
cell C
cell D
cell E
cell F
cell G
cell H
cell I


I am using excel 2003 SP3

Thank you in advanced for any help you guys can provide.

Jason

[Tinbendr]

Welcome to the board!

A larger sampling of the data would have be nice.

[vba]Sub Output2File()

Dim WB As Workbook
Dim WS As Worksheet
Dim LastRow As Long
Dim A As Long
Dim B As Long

Set WB = ActiveWorkbook
Set WS = WB.Worksheets(1)

With WS
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

For A = 1 To LastRow
Open .Range("A" & A) & ".mtag" For Output As #1
For B = 2 To 9
Write #1, .Cells(A, B).Value
Next
Close #1
Next
End With

End Sub[/vba]

[snb]

or
[vba]
sub snb()
sn=thisworkbook.sheets(1).cells(1).currentregion

with createobject("scripting.filesystemobject")
for j=1 to ubound(sn)
.createtextfile(sn(j,1) & ".mtag").write join(application.index(sn,j),vbCrLf)
next
end with
End sub[/vba]

[jramsey]

Thank you for the fast responses. I tried to set my output path the C:\test\ but I keep getting a syntax error so I am obviously missing something. How much more data are you looking for?

[snb]

[VBA]Sub snb()
sn=thisworkbook.sheets(1).cells(1).currentregion

With createobject("scripting.filesystemobject")
For j=1 To UBound(sn)
.createtextfile("C:\Text\" & sn(j,1) & ".mtag").write join(application.index(sn,j),vbCrLf)
Next
End With
End Sub[/VBA]

[jramsey]

Well i am still getting a runtime error '76':
path not found

[jramsey]

Tinbendr, I added "c:\text\" & to the open line and I can get it to output the files I need. However it is adding a bunch of quotes (") to the beginning and end of the lines and its adding Additional quotes to the text already in quotes. so the data in cell 2 is: <meta http-equiv="Content-type" content="text/html;charset=UTF-8"> but what it is writing to the file is: "<meta http-equiv=""Content-type"" content=""text/html;charset=UTF-8"">"

Thank you for the help

Jason

[Kenneth Hobs]

Use Print rather than Write.

[jramsey]

Kenneth, now i am back to "path not found"

[snb]

Well I think it's obvious no such path as C:\Text exists:



[vba]

Sub snb()
if dir("C:\Text",16)="" then mkdir "C:\Text"

sn=thisworkbook.sheets(1).cells(1).currentregion

With createobject("scripting.filesystemobject")
For j=1 To UBound(sn)
.createtextfile("C:\Text\" & sn(j,1) & ".mtag").write join(application.index(sn,j),vbCrLf)
Next
End With
End Sub

[/vba]

[jramsey]

it did and does. and i still get a path not found with your added lines.

[snb]

Did you test my suggestion ?

check whether there are any values in sheets(1), adapt teh code to your situation; e.g sheets("data") if that is the name of the sheet that contains the data.
You best post a sample workbook.

[jramsey]

here is a sample of the file i am working with

[jramsey]

and here is what i would like to get out as a text file (.mtag) i had to zip it to get it to upload.

[Kenneth Hobs]

Of course you do need to set a path to store them.

[vba]Sub Output2File()
Dim WB As Workbook
Dim WS As Worksheet
Dim LastRow As Long
Dim A As Long
Dim B As Long
Dim fn As Integer
Dim idPath As String

idPath = ThisWorkbook.Path & "\"
Set WB = ActiveWorkbook
Set WS = WB.Worksheets(1)

With WS
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

For A = 1 To LastRow
fn = FreeFile
Open idPath & .Range("A" & A) & ".mtag" For Output As #fn
For B = 2 To 9
Print #fn, .Cells(A, B).Value
Next
Close #fn
Next
End With
End Sub[/vba]

Obviously, snb's code works fine too. Just be sure to change the value of idPath if needed.
[VBA]Sub snb()
Dim idPath As String, sn As Variant, j As Long

idPath = ThisWorkbook.Path & "\"
If Dir(idPath, vbDirectory) = "" Then MkDir idPath

sn = ThisWorkbook.Sheets(1).Cells(1).CurrentRegion

With CreateObject("scripting.filesystemobject")
For j = 1 To UBound(sn)
.createtextfile(idPath & sn(j, 1) & ".mtag").write Join(Application.Index(sn, j), vbCrLf)
Next j
End With
End Sub[/VBA]

[jramsey]

well that did it.. THANK YOU!!!!