PDA

View Full Version : Save each sheet as a text file



shs91
10-24-2016, 10:21 AM
Hi all :)

I hope you guys can help me with this one.

I have a workbook with a number of sheets which I want to save as text files. I have tried google it, but none of the really worked.

Basically in pseudo code the code should do the following:

//
For each sheet in workbook

Save sheet as text file (name should be sheet name and same location as workbook)

Next sheet

//

Please note I have a lot of (1 million active cells per sheet) data, so the code should be efficient.
I know this seems really easy, but somehow it doesn't work for me.

Please help me.

Thanks Simon :)

YasserKhalil
10-24-2016, 12:08 PM
May be

Sub Export_Sheets_To_Text_Files() Dim Ws As Worksheet
Dim strFile As String


With Application
.ScreenUpdating = False
For Each Ws In .ActiveWorkbook.Worksheets
Ws.Copy
strFile = ThisWorkbook.Path & "\" & Ws.Name & ".txt"
.ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlText
.ActiveWorkbook.Saved = True
.ActiveWorkbook.Close
Next Ws
.ScreenUpdating = True
End With

MsgBox "Done...", 64
End Sub