Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

Kutools for Excel, with more than 120 handy Excel functions, enhance working efficiency and save working time.

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 120 handy Excel functions, enhance your working efficiency and save your working time.

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 Enterprise > 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 120 handy Excel functions, enhance your working efficiency and save your working time.

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

pay attention1If you are interested in this addi-in, click here to download the 60-days free trial.


Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
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.