Microsoft Excel - Save As CSV (Macro)
This is a macro which will help you to save a worksheet to CSV (Comma-Separated Values) using a shortcut key. The trick is to execute a customized macro named "SaveAsCSV". You may further modify this macro to work for other format.
Discussion below will be focused on using CSV format.
The contents of the Macro are given as below:
' The MIT License (MIT)
' Copyright (c) 2013 Code Art Engineering
' Permission is hereby granted, free of charge, to any person obtaining a copy of
' this software and associated documentation files (the "Software"), to deal in
' the Software without restriction, including without limitation the rights to
' use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of
' the Software, and to permit persons to whom the Software is furnished to do so,
' subject to the following conditions:
' The above copyright notice and this permission notice shall be included in all
' copies or substantial portions of the Software.
' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
' IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS
' FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR
' COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER
' IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
' CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
Sub SaveAsCSV()
ActiveWorkbook.Save
Dim fileName As String
fileName = ActiveWorkbook.FullName
Dim sheetName As String
sheetName = ActiveSheet.Name
Dim csvFileName As String
csvFileName = ActiveWorkbook.Path + "\" + sheetName
If (Right(LCase(csvFileName), 3) <> "csv") Then
csvFileName = csvFileName + ".csv"
End If
ActiveSheet.SaveAs csvFileName, XlFileFormat.xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=True
Workbooks.Open fileName
End Sub
Download this Personal.xlsm and place it to the following location:
C:\Program Files (x86)\Microsoft Office\Office12\XLSTART\
The Personal.xlsm will be loaded next time when you load
The SaveAsCSV macro had been bind to short cut "Ctrl + Shift + S"
Discussion below will be focused on using CSV format.
How does it work?
- Save active excel file.
- Get file name for active excel file.
- Get current sheet name.
- Check if sheet name contains desired extension (.csv)
- Append file extension if required.
- Save current sheet as desired format (CSV).
- Close current CSV File.
- Reload active excel file.
The contents of the Macro are given as below:
' The MIT License (MIT)
' Copyright (c) 2013 Code Art Engineering
' Permission is hereby granted, free of charge, to any person obtaining a copy of
' this software and associated documentation files (the "Software"), to deal in
' the Software without restriction, including without limitation the rights to
' use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of
' the Software, and to permit persons to whom the Software is furnished to do so,
' subject to the following conditions:
' The above copyright notice and this permission notice shall be included in all
' copies or substantial portions of the Software.
' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
' IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS
' FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR
' COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER
' IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
' CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
Sub SaveAsCSV()
ActiveWorkbook.Save
Dim fileName As String
fileName = ActiveWorkbook.FullName
Dim sheetName As String
sheetName = ActiveSheet.Name
Dim csvFileName As String
csvFileName = ActiveWorkbook.Path + "\" + sheetName
If (Right(LCase(csvFileName), 3) <> "csv") Then
csvFileName = csvFileName + ".csv"
End If
ActiveSheet.SaveAs csvFileName, XlFileFormat.xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=True
Workbooks.Open fileName
End Sub
How To Use
1. Using the Personal.xlsm (Created using MSOffice 2007) - The easy way
Download this Personal.xlsm and place it to the following location:
C:\Program Files (x86)\Microsoft Office\Office12\XLSTART\
The Personal.xlsm will be loaded next time when you load
The SaveAsCSV macro had been bind to short cut "Ctrl + Shift + S"
2. Create from scratch
- Create new excel file.
- Create new macro "SaveAsCSV", assign short-cut key.
- Copy and paste above codes.
- Save excel file as any desire name <FileName>.xls 0
(Note: For MS Office 2007, you have to save it as xlsm) - Copy the file to XLSTART folder. (See above)
- Restart Microsoft Excel. The file contains the macro will be automatically loaded.
- Hide this file (prevent accidentally modified).
- Close application and save changed to this file.
- Done.
