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 Sub

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

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.

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.