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.
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"
ActiveSheet.SaveAs csvFileName, XlFileFormat.xlCSV, CreateBackup:=False
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.