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
Start Microsoft Excel and open your workbook.
Press "ALT+F8" to open the Macros window. Type "ExportSheetsToCSV" in the Macro name box and then click on "Create."
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.
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
Close the VBA edit to go back to your workbook.
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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for