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
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:
