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 good idea3

Export a selection of sheet as text file with Kutools for Excel


arrow blue right bubble 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:

doc save as text file 1

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.


arrow blue right bubble 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:

doc save as text file 2

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:

doc save as text file 3

3. Click Split, and then select a folder to put the text files from Browse For Folder dialog. See screenshot:

doc save as text file 4

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


arrow blue right bubble 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:

doc save as text file 5

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.

doc save as text file 6

3. Click Ok. Now name the exported text file in the popping Export Range to File dialog box, and click the OK button.

doc save as text file 7

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.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.