Macro that stores all sheets as tab-delimited text files

  • Thread starter Thread starter b.d.jensen
  • Start date Start date
B

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
 
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
 
Back
Top