Excel sheets to text files?

[KennyKenKen]

Hi can you help me fill in the code to export my sheets to text (tab) delimited files? This is what I've done so for.

Sub Macro1()
Dim wksht As Worksheet
Dim i As Long
Dim sFilename As String
Dim wkshtnames() 'This is an array definition
i = 0
For Each wksht In ActiveWorkbook.Worksheets
i = i + 1
ReDim Preserve wkshtnames(1 To i)
wkshtnames(i) = wksht.Name

'strFilename = "C:\" & wkshtnames(i) & ".txt"

'sPath = "C:\"

'Open strFilename For Binary As #1
'Put #1, , TextArray 'writes to file
'Close #1

Next wksht

For i = LBound(wkshtnames) To UBound(wkshtnames)
MsgBox wkshtnames(i)
Next i
End Sub

THANK YOU

[Webtest]

Kenny:

I'm not proficient enough to read your code straight through, and I'm in a rush this morning ... here are a couple of thoughts ...

In your sheet loop ...

(you can record a macro to see how to code these) ...

Edit > Move or Copy Sheet > To Book: (new book)
This gets away from the problem of SaveAs not working for a book with multiple sheets.

then:

SaveAs your "new book" in a file as a Tab Delimited Text file with your desired filename:
ChDir "C:\YourPath"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\guawd1\Desktop\JUNKjunkJUNK.txt", FileFormat:= _
xlText, CreateBackup:=False

Hope this helps a little ... I'll check back later.

[Ecniv]

Sub Macro1()
    Dim wksht As Worksheet
    Dim i As Long
    Dim sFilename As String, sData as string
    Dim wkshtnames() 'This is an array definition
    Dim lngX as Long, lngY as Long
    
    i = 0
    For Each wksht In ActiveWorkbook.Worksheets
        i = i + 1
        ReDim Preserve wkshtnames(1 To i)
        wkshtnames(i) = wksht.Name

        strFilename = "C:\" & wkshtnames(i) & ".txt"

        Close #1

        Open strFilename For Output As #1
        
        for lngX = 1 to 10
            sData = ""
            for lngY = 1 to 10
                sData = sData & iif(lngY>1,vbtab,"") & wksht.cells(lngY,lngX).value
            next
            print #1,sData
        next

        Close #1

    Next wksht

    For i = LBound(wkshtnames) To UBound(wkshtnames)
        MsgBox wkshtnames(i)
    Next i
End Sub

It would be something like this, but you'd have to specify the max rows and max cols (instead of 10)

[KennyKenKen]

This is my latest working version!!

Thanks

Sub export()
Dim FName As String
Dim wksht As Worksheet
Dim i As Long
Dim wkshtnames() 'This is an array definition

Application.ScreenUpdating = False
On Error GoTo EndMacro:

i = 0
For Each wksht In ActiveWorkbook.Worksheets
i = i + 1
ReDim Preserve wkshtnames(1 To i)
wkshtnames(i) = wksht.Name
FName = "C:\temp\" & wkshtnames(i) & ".txt"
wksht.Activate
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:= _
xlText, CreateBackup:=False
Next wksht
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

[Webtest]

Ken:

(using SQUARE brackets [], not {} the curly braces shown)
When you post code, prefix your code with the text {code}, and put the text{/code} at the end. That way your code will be posted in "pretty" format.

(Hey, does anybody know how to trivially "UNTAG" the code and /code tags in the body of a message?)

[Webtest]

Kenny:

As far as I know, you can NOT SaveAs a workbook containing multiple sheets as an xlText file (I may be wrong). That's why I suggested doing an Edit Copy the sheet to a new book first, because the new book will only contain the copied sheet and no other(s).