I regularily work with workbooks that contain well over 20 worksheets per
book. After working with the data I always have to save each 'sheet' as an
individual *.txt file. All the sheets are saved using the worksheet name.

Is there any way to automate this process, ie. save all worksheets to a
respective *.txt file. For example, 20 sheets in a book, will result in 20
seperate *.txt files.

Steve

You could use a macro:

Option Explicit
Sub Testme02()

Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets
Wks.Copy 'to a new workbook
With ActiveSheet 'the new sheet in the new workbook
.Parent.SaveAs Filename:="C:\temp\" & .Name & ".txt", _
FileFormat:=xlTextWindows
.Parent.Close savechanges:=False
End With
Next Wks

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

This also assumes that the worksheet names are valid OS names and that the
C:\Temp folder already exists.




stevieb wrote:

I regularily work with workbooks that contain well over 20 worksheets per
book. After working with the data I always have to save each 'sheet' as an
individual *.txt file. All the sheets are saved using the worksheet name.

Is there any way to automate this process, ie. save all worksheets to a
respective *.txt file. For example, 20 sheets in a book, will result in 20
seperate *.txt files.

Steve

--

Dave Peterson

I think the code you wrote is a little to complicated for the task. I dont
see why you cant traverse through the active workbook, and just save each
file. Would it possible to do that instead of making a new workbook each
time?

"Dave Peterson" wrote:

You could use a macro:

Option Explicit
Sub Testme02()

Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets
Wks.Copy 'to a new workbook
With ActiveSheet 'the new sheet in the new workbook
.Parent.SaveAs Filename:="C:\temp\" & .Name & ".txt", _
FileFormat:=xlTextWindows
.Parent.Close savechanges:=False
End With
Next Wks

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

This also assumes that the worksheet names are valid OS names and that the
C:\Temp folder already exists.




stevieb wrote:

I regularily work with workbooks that contain well over 20 worksheets per
book. After working with the data I always have to save each 'sheet' as an
individual *.txt file. All the sheets are saved using the worksheet name.

Is there any way to automate this process, ie. save all worksheets to a
respective *.txt file. For example, 20 sheets in a book, will result in 20
seperate *.txt files.

Steve

--

Dave Peterson

If you go through the sheets in the active workbook and save each as a .txt
file, watch what happens to the filename each time you do the save.

Or just look at the filename after the last worksheet has been saved.

It's much easier to avoid the problem than to work around it (in my opinion).

stevieb wrote:

I think the code you wrote is a little to complicated for the task. I dont
see why you cant traverse through the active workbook, and just save each
file. Would it possible to do that instead of making a new workbook each
time?

"Dave Peterson" wrote:

You could use a macro:

Option Explicit
Sub Testme02()

Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets
Wks.Copy 'to a new workbook
With ActiveSheet 'the new sheet in the new workbook
.Parent.SaveAs Filename:="C:\temp\" & .Name & ".txt", _
FileFormat:=xlTextWindows
.Parent.Close savechanges:=False
End With
Next Wks

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

This also assumes that the worksheet names are valid OS names and that the
C:\Temp folder already exists.




stevieb wrote:

I regularily work with workbooks that contain well over 20 worksheets per
book. After working with the data I always have to save each 'sheet' as an
individual *.txt file. All the sheets are saved using the worksheet name.

Is there any way to automate this process, ie. save all worksheets to a
respective *.txt file. For example, 20 sheets in a book, will result in 20
seperate *.txt files.

Steve

--

Dave Peterson


--

Dave Peterson