Basic macro ? I'm new to this.

K

Katie

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.
 
B

Bob Phillips

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)
 
S

SmilingPolitely

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.
 
S

SmilingPolitely

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
 
Top