Export Multiple Excel Worksheets to CSV or Text Using VBA

The Problem

Using the basic functions in Excel, you are only able to save your entire worksheet as a text file or as a CSV. So, how do you convert multiple Excel sheets into separate CSV or text files? That’s why we created the step-by-step guide below to share how we solved this problem.

Want to export to Text files instead? Jump to the section here: Convert Multiple Excel Sheets to Text Files. Otherwise, follow the CSV steps below.

How to Convert Multiple Excel Sheets into CSV Files

Step 1 (CSV) - Open the VBA Editor

First, make sure your Excel spreadsheet is open and press ALT + F11 at the same time to open the Microsoft Visual Basic Application (VBA) window. If ALT + F11 does not work, use this alternative:
Under the Developer tab, click Visual Basic (left side) to open the VBA editor.

Step 2 (CSV) - Insert a Module and Paste the VBA Code

In the VBA editor, click Insert → Module. Then copy/paste the following code into the Module window:

        Sub ExportSheetsToText()
            Dim xWs As Worksheet
            Dim xTextFile As String
               For Each xWs In Application.ActiveWorkbook.Worksheets
                 xWs.Copy
                 xTextFile = CurDir & "\" & xWs.Name & ".txt"
                 Application.ActiveWorkbook.SaveAs Filename:=xTextFile, FileFormat:=xlText
                 Application.ActiveWorkbook.Saved = True
                 Application.ActiveWorkbook.Close
              Next
          End Sub

It will look like this:

Step 3 (CSV) - Run the Macro

After inserting the code, press F5 to run it. Excel will export each worksheet as a separate CSV file. You should see the exported files in your Documents folder, similar to this screenshot:

Converting Multiple Excel Sheets into Text Files

The Text export process uses the same VBA editor steps as the CSV section above: open the VBA editor (ALT + F11), then insert a module (Insert → Module). The only difference is the macro code and the output file extension.

Step 2 (Text) - Paste the Text Export VBA Code

In your module window, copy/paste the following VBA code:

Sub ExportSheetsToText()
    Dim xWs As Worksheet
    Dim xTextFile As String
    For Each xWs In Application.ActiveWorkbook.Worksheets
        xWs.Copy
        xTextFile = CurDir & "\" & xWs.Name & ".txt"
        Application.ActiveWorkbook.SaveAs Filename:=xTextFile, FileFormat:=xlText
        Application.ActiveWorkbook.Saved = True
        Application.ActiveWorkbook.Close
    Next
End Sub

Step 3 (Text) - Run the Macro

After inserting the code, press F5 to run it. Excel will export each worksheet as a separate text file. You should see the exported files in your Documents folder, similar to this screenshot:

Did you know that we also do Integration Solutions?

Chipkin has integration solutions for almost every situation. We specialize in network protocol communications and have over 20+ years of experience. Click for more information: