Macro that stores all sheets as tab-delimited text files

[b.d.jensen]

Hi!
I'm trying to write an macro that converts one single xls-file
to multiple text file (tab delimited) with filenames corrsponding to
the names of the sheets.

How to do that?

I recorded my actions:
Sub GemAsTxt()
ActiveWorkbook.SaveAs Filename:= _
"H:\Develop\work\TestData\Testmodel.txt", FileFormat:= _
xlText, CreateBackup:=False
End Sub

But what I need is:
Sub GemAsTxt()
for all sheets:
ActiveWorkbook.SaveAs Filename:= _

"<directoryOfCurrentExcelFile>\<excelfileWithoutFileType>\<sheetname>",
FileFormat:= _
xlText, CreateBackup:=False
End Sub

 

[Guest]

Doesn't work unless a directory matching the name of the workbook (without
the .xls) already exists

Sub SaveEm()
Dim strPath As String
Dim ws As Worksheet

With ActiveWorkbook
strPath = .Path & "\" & WorksheetFunction.Substitute(.Name, ".xls", "\")
For Each ws In .Worksheets
ws.SaveAs strPath & ws.Name & ".txt", xlTextWindows
Next
End With
End Sub

 

[b.d.jensen]

Thank you
I'll try it (-:
/Bjoern