How to Save or Export Each Sheet to Separate CSV Files in Excel

This post will guide you how to save each worksheet to separate CSV files in your workbook in Excel. How do I save multiple or all worksheets to separate csv file in Excel 2013/2016. How to export all worksheets to separate txt files with VBA macro in Excel.

Export Each Sheet to Separate CSV Files


Assuming that you have a workbook that has four different worksheets, sheet1, sheet2, sheet3, and sheet4. And You want each worksheet as its own CSV file and we also want the CSV file name to take into account its Excel Source as well as the workbook where it originates. To achieve the result of exporting multiple worksheets separate csv files, you have to use VBA Macro to save all worksheets in the current workbook to separated csv files. Here are the steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

el v

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook

      SaveToDirectory = "D:\tmp\"

      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name, xlCSV
      Next

 Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

export each sheet to csv1

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

export each sheet to csv3

Step6: you would see that all worksheet has been converted to a single CSV files in the specified directory. This example will save csv file into D:\tmp\ directory. You need to change the variable “SaveToDirectory ” as you need. or you can also create a temp directory called tmp in disk D partition.

export each sheet to csv4

Export Each Sheet to Separate Text Files


If you want only convert each worksheet to separate text files for your active workbook, you can also use an Excel VBA Macro to achieve the result. Just using the following VBA code:

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook

      SaveToDirectory = "D:\tmp\"

      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name & ".txt"
      Next

 Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=xlText
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub
let’s see the result:
export each sheet to csv5

 

You might also like:

Sidebar