How to convert all excel sheets to CSV with macros

Written by brian valle
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to convert all excel sheets to CSV with macros
Converting a spreadsheet to CSV will strip formulas and styles from it. (Stockbyte/Stockbyte/Getty Images)

In Microsoft Excel, you can easily convert a workbook to CSV by choosing an appropriate file type in the "Save As" dialogue box. Using this method, only the active sheet will be converted. This is because CSV files can only contain one sheet. To convert multiple sheets into CSV, you will need to export them one by one. This involves activating each sheet, moving it to an empty workbook and then saving it as a CSV file. To make this task easier and more convenient, use macros.

Skill level:
Moderately Easy

Other People Are Reading

Instructions

  1. 1

    Start Microsoft Excel and open your workbook.

  2. 2

    Press "ALT+F8" to open the Macros window. Type "ExportSheetsToCSV" in the Macro name box and then click on "Create."

  3. 3

    Enter the following code in the VBA editor. This macro will create the CSV files in the same folder as your workbook using sheet names for file names.

    Sub ExportSheetsToCSV()

    Dim wSheet As Worksheet

    Dim csvFile As String

    For Each wSheet In Worksheets

    On Error Resume Next
    
    wSheet.Copy
    
    csvFile = CurDir & "\" & wSheet.Name & ".csv"
    
    ActiveWorkbook.SaveAs Filename:=csvFile, _
    
        FileFormat:=xlCSV, CreateBackup:=False
    
    ActiveWorkbook.Saved = True
    
    ActiveWorkbook.Close
    

    Next wSheet

    End Sub

  4. 4

    Close the VBA edit to go back to your workbook.

  5. 5

    Press "ALT+F8" again to open the Macros window. Select "ExportSheetsToCSV" from the list and then click on "Run." Wait for Excel to finish converting the sheets.

Tips and warnings

  • If you wish to save the CSV files in a different folder, replace "CurDir" in Line 9 with the path to your preferred folder. You may also move the workbook to your preferred folder before running the macro.
  • By default, macros are disabled for Microsoft Excel. To enable them, click the Microsoft Office button, go to "Excel Options" and then make sure that "Show Developer tab in the Ribbon" is checked. Click "OK." In some cases, you may need to restart MS Excel for the changes to take effect.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.