Exporting each row as tab-delimited text

[DerekLee1]

Hi. I'm trying to find a macro that will export or save each row in a spreadsheet to a tab-delimited text file. Any help would be really appreciated. Thanks!

[jolivanes]

Did you try the code from barryleajo

http://www.excelforum.com/excel-prog...html?p=3311710

and try replacing this

Print #1, ws.Cells(rownum, colnum) & " ";

with this?

Print #1, ws.Cells(rownum, colnum) & vbTab;

or this?

Print #1, ws.Cells(rownum, colnum) & vbCr;

[DerekLee1]

YES! YES! This worked! Sorry about the two-thread issue. It was not my intent to flood the forum. One more question, if you don't mind. I would like the name of the file to be the row number instead of the data in Column A. How would I incorporate that into the macro? Thanks so very much!

[Solus Rankin]

Try changing:

filename = .Cells(rownum, 1).Value

To

filename = .Cells(rownum, 1).Row

Untested.

[DerekLee1]

Yes! Thanks so much for all your help. Saved me hours of work, and maybe more than that in the future.

Last question, I promise. With this code, the worksheet to be split always has to be named "Sheet1" Can it be changed so that it just works with the active sheet instead of the names in the code and worksheet needing to match?

Sub SplitRowsToTXT()

Dim firstrow As Integer, rownum As Integer, colnum As Integer
Dim fileName As String
Dim Filelocation As String
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

Filelocation = "C:\Temp\"

    With ws
        rownum = 1
        While .Cells(rownum, 1) <> ""
            fileName = .Cells(rownum, 1).Row
            Open (Filelocation & fileName & ".txt") For Output As #1
            
            colnum = 1
            While .Cells(rownum, colnum) <> 0
                 Print #1, ws.Cells(rownum, colnum) & vbTab;
                colnum = colnum + 1
            Wend
            Close #1
            rownum = rownum + 1
        Wend
        
    End With

End Sub

[jolivanes]

Have you tried changing this

Set ws = Worksheets("Sheet1")

to this?

Set ws = ActiveSheet

[Solus Rankin]

DerekLee1

For future reference, if your original post is answered, please mark your thread as [solved] and start a new post for new questions.

[HaHoBe]

Hi, XeRo Solus,

as I am curious could you please point the difference between your sample code

filename = .Cells(rownum, 1).Row

and

filename = rownum

AFAIR for the Cells rownum has either to be Variant (holding a number), Byte, Integer or Long and will hold the Row.

Ciao,
Holger

[Solus Rankin]

Holger,

You're absolutely correct. I didn't read into it enough.

[DerekLee1]

Both ways worked for me, but I went with Holger's simpler code. Also for concern of what a blank cell might do to the exports. Thanks all. Got this working perfectly now.