Sunday, October 20, 2013

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.

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.

Appreciation

Do you like our work? Appreciate if you could help to buy us a cup of coffee. Thank you.