How to Export Multiple Excel sheets to CSV or Text Files Using Visual Basic

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 as a way to share with others how we solved this problem.

Click here if you want to convert to Text files . Or, if you want to convert to CSV, please follow the instructions below:

How to Convert Multiple Excel Sheets into CSV Files:

Step 1 - Open VBA Window

  • First, make sure your Excel Spreadsheet is open and press ALT + F11 at the same time in order to open the Microsoft Visual Basic Application (VBA) window. If you cannot open the VBA window by pressing “ALT + F11”, there’s an alternative way to do it:
  • Under the Developer Tab, click "Visual Basic" on the left side and that action will open it:

Step 2 - Input VSB Code

  • Now that your Microsoft Visual Basic Application is open, you need to click Insert --> Module. Then, copy/paste this code into your Module Window:

                Sub ExportSheetsToCSV()
                    Dim xWs As Worksheet
                    Dim xcsvFile As String
                    For Each xWs In Application.ActiveWorkbook.Worksheets
                        xWs.Copy
                        xcsvFile = CurDir & "\" & xWs.Name & ".csv"
                        Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
                        FileFormat:=xlCSV, CreateBackup:=False
                        Application.ActiveWorkbook.Saved = True
                       Application.ActiveWorkbook.Close
                   Next
               End Sub

  • It will look like this:

Step 3 - Final Step

  • After you insert the code, you need to press F5 to make it run. Then, you will see that all the exported CSV Files are located in your Documents Folder - like in this screenshot:
Converting Multiple Excel Sheets into Text Files

Step 1 - Open VBA Window

  • First, make sure your Excel Spreadsheet is open and press ALT + F11 at the same time in order to open the Microsoft Visual Basic Application (VBA) window. If you cannot open the VBA window by pressing “ALT + F11”, there’s an alternative way to do it:
  • Under the Developer Tab, click "Visual Basic" on the left side and that action will open it:

Step 2 - Input VSB Code

  • Now that your Microsoft Visual Basic Application is open, you need to click Insert --> Module. Then, copy/paste this code into your 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                          
        

Step 3 - Final Step

  • After you insert the code, you need to press F5 to make it run. Then, you will see that all the exported CSV Files are located in your Documents Folder - like in this screenshot:

Gateway Selector

Select your combination of protocols.
Protocol 1
Protocol 2

Contact Us

Contact us via phone (+1 866-383-1657) or leave a detailed message below for sales, support, or any other needs

*Required Field
I'd like to receive the newsletter. *Check email for confirmation.
*Required Field
Preferred Time To Be Contacted (PST)
8:00am - 12:00pm 12:00pm - 5:00pm