Sheets To TXT

PROBLEM 1:

"I have a workbook with 30 sheets, and I need each individual sheet saved as a delimited text file (delimiter is tab) in the same folder as the excel file, named after the name of each corresponding worksheet. Eg. Worksheet name is "B", then I need all the content of the worksheet saved in file B.txt with delimiter tab. once that sheet is done, move on to the next sheet. However, there are 3 exceptions of sheets which I do not want to be processed: "A-K" or "L-S" or "T-Z". Al other sheets shall be processed."

SPECIFICATIONS:

All sheet saved into same folder as original workbook
All sheets saved as text file - named for the original sheetname
Certain sheets must be skipped

CODE

Sub SheetsToText()
'Jerry Beaucaire (12/11/2009)
Dim ws As Worksheet, wb As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False

wb = ActiveWorkbook.Name

For Each ws In Worksheets
    If ws.Name <> "A-K" And ws.Name <> "L-S" And ws.Name <> "T-Z" Then
        ws.Activate
        ActiveWorkbook.SaveAs Filename:=ws.Name & ".txt", FileFormat:=xlText, CreateBackup:=False
        'For Excel 2007+ use   FileFormat:=xlTextWindows
    End If
Next ws

ActiveWorkbook.SaveAs Filename:=wb, FileFormat:=xlNormal, CreateBackup:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

PROBLEM 2:

"I have a worksheet with many columns of data. I need to create a comma-delimited text file with the values from columns A, B and C only"

SPECIFICATIONS:

All rows must be evaluated
Comma-delimited file created, one row for each row in the worksheet

CODE

Sub TxtWriter()
'Jerry Beaucaire (10/23/2009)
'Write three cells per row from all worksheets into a single comma-delimited text file
Dim cell As Range, LR As Long
Dim ws As Worksheet, s As String
Open "C:\My Documents\Test\myfile.txt" For Append As #1

    For Each ws In Worksheets
        LR = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row
        For Each cell In ws.Range("A1:A" & LR)
            s = cell.Text & "," & cell.Offset(0, 1).Text & "," & cell.Offset(0, 2).Text
            Print #1, s
        Next cell
    Next ws

Close #1
End Sub

Nothing says "thanks" like a steak dinner!

Comments