Parse Functions‎ > ‎

### 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:
1. All sheet saved into same folder as original workbook
2. All sheets saved as text file - named for the original sheetname
3. Certain sheets must be skipped

#### CODE

Sub SheetsToText()
'Jerry Beaucaire  (12/11/2009)
Dim ws As Worksheet, wb As String
Application.ScreenUpdating = 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
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:
1. All rows must be evaluated
2. 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!