How to save each sheet as separate text file from a workbook?
When you want to save a worksheet as text file, you may use Excel’s Save As function to save the worksheet as a text file. But if you want to save every sheet of a workbook as a separate text file, the steps of Save As must irritate you. Here I introduce some interesting tricks for you to quickly solve this boring job in Excel.
Save a sheet as text file with VBA
Save each sheet of a workbook as separate text file with Kutools for Excel 
Export a selection of sheet as text file with Kutools for Excel
Save a sheet as text file with VBA
If you do not like to use Save As function to save sheet as text file, you can use VBA code.
1. Activate the sheet you want to save as text file.
2. Press Alt + F11 keys to display Microsoft Visual Basic for Applications window.
3. Click Insert > Module from the window, and then paste below VBA code to the Module.
VBA: Save sheet as text file.
Sub SaveSheetToTxt()
'Updateby20150910
Dim xRet As Long
Dim xFileName As Variant
On Error GoTo ErrHandler:
xFileName = Application.GetSaveAsFilename(ActiveSheet.Name, "TXT File (*.txt), *.txt", , "Kutools for Excel")
If xFileName = False Then Exit Sub
If Dir(xFileName) <> "" Then
xRet = MsgBox("File '" & xFileName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel")
If xRet <> vbYes Then
Exit Sub
Else
Kill xFileName
End If
End If
ActiveSheet.Copy
ActiveWorkbook.SaveAs xFileName, xlUnicodeText
If ActiveWorkbook.Name <> ThisWorkbook.Name Then
ActiveWorkbook.Close False
End If
My_Exit:
Exit Sub
ErrHandler:
MsgBox Err.Description, , "Kutools for Excel"
End Sub4. Click Run button to execute the code, and then in the Kutools for Excel dialog, select a folder to put the text file, and specify a name for the text file. See screenshot:
5. Click Save. Now the active sheet is save as text file.
Tip: with the VBA, you only can save one sheet as text file at once time.
Save each sheet of a workbook as separate text file with Kutools for Excel
If you have a large workbook with hundreds of sheets needed to save to separate text files, the VBA method is also time-wasted. In this case, you can try to use Kutools for Excel’s Split Workbook to handle with this job, which will give you an unexpected surprise.
| Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. |
After free installing Kutools for Excel, please do as below:
1. Enable the workbook you want to save its sheets as independent text files, and then click Kutools Plus > Workbook > Split Workbook. See screenshot:
2. In the Split Workbook dialog, select the sheets you want to split and save as text files, in default, all the sheets are selected, and then check Specify save format, and select Unicode Text(*.txt ) from below drop down list. See screenshot:
3. Click Split, and then select a folder to put the text files from Browse For Folder dialog. See screenshot:
4. Click OK. Now each sheet of the workbook is saved as a separate text file in the selected folder.
With Split Workbook of Kutools for Excel, you can split a workbook and save them as separate xls files, xlsx files, PDF files or CSV files, click here to know more about this utility.
Click here to free download Kutools for Excel
Export a selection of sheet as text file with Kutools for Excel
With Kutools for Excel, you also can export a selection of a sheet as text file by using its Export Range to File.
| Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. |
After free installing Kutools for Excel, please do as below:
1. Select a range or ranges you want to save as text file, and click Kutools Plus > Import/Export > Export Range to File. See screenshot:
2. Then in the popping dialog, check Unicode Text option under File format section, then go to Text options section to check an option as you need, and then select a saving location.
3. Click Ok. Now name the exported text file in the popping Export Range to File dialog box, and click the OK button.
So far the selection has been exported as a separate text file.
There are more than 120 tools in Kutools for Excel, to install it and have a 60-days trial, then you may find many undreamed useful functions.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
