How to do SaveAs and Change FileFormat by using Excel VBA Code?

Read this article to get detail information to change excel file format. It is very useful requirement in various excel projects. Most of the excel reports uses input from csv files. Several times we need to convert .csv files into .xls or xlsx which is very tedious task. Usually, it consumes lot of hours for file conversion. However, we can simply automate this file conversion task writing excel VBA code. which we save couple of hours.


Given below is simple example to convert .csv file into excel:


Sub Convert_CSV_To_Excel()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com
' Convert each csv file to excel from given list of files

Dim fName As String
Dim Dir_Path As String
Dim New_fName As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
    'Folder path where .csv file is available
    Dir_Path = "C:\Users\Dreams\Desktop\Blog" & "\"

    'Name of .csv file to convert
    fName ="abc.csv"

    'Open .csv file
    Workbooks.Open  Dir_Path  & fName
 
    'Save file into .xls format
    ActiveWorkbook.SaveAs Filename:=Dir_Path & New_fName & ".xls", _
    FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    
    ActiveWorkbook.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

These are the main file formats in Excel 2007-2013:

51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

Important to know this because SaveAs requires you to provide both the FileFormatparameter and the correct file extension.

You can convert multiple files using File Format Converter Add-ins at once: 

I have created File Format Converter Add-ins which will help you to convert multiple file at once. Please refer to Download Section.