Hey there, I am trying to write a macro that will export
excel worksheets to tab delimited text files. If I go to
File and Saveas I can save the worksheet fine but not the
whole work book. Does anyone know of a way I can write a
macro to select all the worksheets and export them all to
a text file? Or perhaps multiple text files, you know
one for each sheet? Any help would be greatly
appreciated. I'm new to this whole thing and it is
harder than I thought it would be. Thanks a bunch!
Mad love, Kate.

Hi Katie,

Simplest way is to copy the worksheets one at a time to a new workbook
(right-click on the sheet tab to get a menu), and then save these as text
files. No macro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Katie" wrote in message
...
Hey there, I am trying to write a macro that will export
excel worksheets to tab delimited text files. If I go to
File and Saveas I can save the worksheet fine but not the
whole work book. Does anyone know of a way I can write a
macro to select all the worksheets and export them all to
a text file? Or perhaps multiple text files, you know
one for each sheet? Any help would be greatly
appreciated. I'm new to this whole thing and it is
harder than I thought it would be. Thanks a bunch!
Mad love, Kate.

You could try something along the lines of:


Sub SaveWorksheets()
'
' This code will save each worksheet as a .csv file with a filename
' which is the same as the worksheet name
'

Dim ws As Worksheet
Dim strFileName As String

For Each ws In Worksheets
strFileName = ws.Name & ".csv"
ActiveWorkbook.SaveAs FileName:=strFileName, FileFormat:=xlCSV,
CreateBackup:=False
Next
End Sub



Hope this helps.


Katie wrote:

Hey there, I am trying to write a macro that will export
excel worksheets to tab delimited text files. If I go to
File and Saveas I can save the worksheet fine but not the
whole work book. Does anyone know of a way I can write a
macro to select all the worksheets and export them all to
a text file? Or perhaps multiple text files, you know
one for each sheet? Any help would be greatly
appreciated. I'm new to this whole thing and it is
harder than I thought it would be. Thanks a bunch!
Mad love, Kate.

mea culpa. You wanted text file not csv....

Sub SaveWorksheets()
'
' This code will save each worksheet as a .txt file with a filename
' which is the same as the worksheet name
'

Dim ws As Worksheet
Dim strFileName As String

For Each ws In Worksheets
strFileName = ws.Name & ".txt"
ActiveWorkbook.SaveAs FileName:=strFileName, FileFormat:=xlTxt,
CreateBackup:=False
Next
End Sub



:-D


SmilingPolitely wrote:

You could try something along the lines of:


Sub SaveWorksheets()
'
' This code will save each worksheet as a .csv file with a filename
' which is the same as the worksheet name
'

Dim ws As Worksheet
Dim strFileName As String

For Each ws In Worksheets
strFileName = ws.Name & ".csv"
ActiveWorkbook.SaveAs FileName:=strFileName, FileFormat:=xlCSV,
CreateBackup:=False
Next
End Sub



Hope this helps.


Katie wrote:

Hey there, I am trying to write a macro that will export excel
worksheets to tab delimited text files. If I go to File and Saveas I
can save the worksheet fine but not the whole work book. Does anyone
know of a way I can write a macro to select all the worksheets and
export them all to a text file? Or perhaps multiple text files, you
know one for each sheet? Any help would be greatly appreciated. I'm
new to this whole thing and it is harder than I thought it would be.
Thanks a bunch! Mad love, Kate.