Macro that stores all sheets as tab-delimited text files
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
" wrote:
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\<excelfileWithoutFi leType\<sheetname",
FileFormat:= _
xlText, CreateBackup:=False
End Sub
|