How to save a excel sheet into .txt file using macro

[rafi_07max]

I want create a macro such that it can save my excel sheet into .txt file.
If my workbook has more than one sheet, then a message should appear and shows all the sheets in my workbook and ask me to choose one sheet that I want to save into .txt file.
Lastly, the user can select the directory where he wants to save the .txt file

[mdmackillop]

Try recording a macro for the first part. For the second, plese post your own code efforts. We are here to assist, rather than do all the work.

[shrivallabha]

Refer to post #11 in the thread,
http://www.vbaexpress.com/forum/showthread.php?t=32670

The workbook has many options that you have asked for! See if it helps. And also you should use search facility especially if you have NOT used it.

[rafi_07max]

Thanks for your comments

I have managed to save sheets separately from a workbook. The codes below do save all the sheets separately. But instead of saving all the sheets,
how can i only select one particular sheet and save it?



Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim DisplayStatusBar As Boolean
DisplayStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
Dim NewFileName As String
Application.StatusBar = ThisWorkbook.Sheets.Count & " Remaining Sheets"
If ThisWorkbook.Sheets.Count <> 1 Then
NewFileName = ThisWorkbook.Path & "\" & ws.Name & ".txt"
ws.Copy
ActiveWorkbook.Sheets(1).Name = "Sheet1"
ActiveWorkbook.SaveAs Filename:=NewFileName, FileFormat:=xlNormal
ActiveWorkbook.Close SaveChanges:=False
Else
NewFileName = ThisWorkbook.Path & "\" & ws.Name & ".txt"
ws.Name = "Sheet1"
End If
Next
Application.DisplayAlerts = True
Application.StatusBar = False
Application.DisplayStatusBar = DisplayStatusBar
Application.ScreenUpdating = True
End Sub

[mdmackillop]

Use a For...Next loop to write all the sheet names to the activesheet.
Select a cell containing a sheet name and use that value instead of ws.name to save.