File formats

Introduction

Excel can handle a lot of different file formats. Throughout the years a number of file formats have been in use. Just think of .xls, .xlsx, .xlsb, all kinds of text file formats, … The usual .xls format that we used to know from Excel versions prior to Excel 2007 has been replace in Excel 2007 with 2 file formats that became popular since that time:

  • .xlsx format for Excel 2007/2010/2013 files without macro procedures
  • .xlsm format for Excel 2007/2010/2013 files with macro procedures

A spreadsheet created in Excel can be saved under a multitude of file formats. Since Excel 2007, PDF is part of the supported file formats and you do not need third-party software to save as a PDF file. Below in this article you can find a big number of choices that you have when you do File > Save As….

file formats

Excel formatExtensionDescription
Excel WorkbookxlsxStandard file format in Excel 2007/2010/2013, based on XML. No macro procedures!
Excel Macro-Enabled Workbookxlsmfile format in Excel 2007/2010/2013, based on XML. Allows macro procedures toe.
Excel Binary WorkbookxlsbBinary file format in Excel 2007/2010/2013.
Excel TemplatexltxStandard file format in Excel 2007/2010/2013 for a Template. No macro procedures!
Excel Macro-Enabled Templatexltmfile format in Excel 2007/2010/2013 for a Template. Allows macro procedures toe.
Excel 97-2003 WorkbookxlsExcel 97-2003 binary file format
Excel 97-2003 TemplatexltExcel 97-2003 binary file format for a Template
Microsoft Excel 5.0/95 Workbook xlsExcel 5.0/95 binary file format
Excel Add-InxlamStandard file format in Excel 2007/2010/2013, based on XML, ffor an Add-In.
Excel 97-2003 Add-InxlaExcel 97-2003 binary file format for an Add-In
Text formatExtensionDescription
Formatted Text (Space delimited)prnSaves as an space-delimited file format
Text (Tab delimited)txtSaves as a tab-delimited text file for Windows (**)
Text (Macintosh)txtSaves as an tab-delimited text file for Macintosh (**)
Text (MS-DOS)txtSaves as an tab-delimited text file for MS-DOS (**)
Unicode TexttxtSaves as Unicode text (*)
CSV (Comma delimited)csvSaves as a comma-delimited text file for Windows (**)
CSV (Macintosh)csvSaves as a comma-delimited text file for Macintosh (**)
CSV (MS-DOS)csvSaves as a comma-delimited text file for MS-DOS (**)
DIF (Data Interchange Format)difSaves as the Data Interchange file format
SYLK (Symbolic Link)slkSaves as the Symbolic Link file format
(*): Saves the entire workbook in this format, not only the active sheet, as the other file formats would do.
(**): Tabs, linebreaks and other characters are interpret correctly.
Other formatsExtensionDescription
PDFpdfPDF is a file format with a fixed layout that allows a file to retain its intended layout when it is viewed or printed
XPS DocumentxpsXPS has the same objective as PDF, yet it is a different file format
OpenDocument SpreadsheetodsThe OpenDocument Spreadsheet file format is a version of the OpenDocument Format (ODF) v1.1
XML Spreadsheet 2003xmlThe XML Spreadsheet 2003 file format
XML DataxmlThe XML Data file format
Single File Web Pagemht / mhtmlSaves (part of) a file as a Web page, including the underlying files and folders
Web Pagehtm / htmlSaves (part of) a file as a Web page, including the underlying files embedded int he Web page

VBA-code

You can save a workbook or worksheet with above-mentioned file formats. How can we do this? Manually and using code.

Excel allows to recognize the file format of opened files. So you can detect whether the active workbook is an Excel 2007/2010/2013 file format without macro procedures (.xlsx extension). Write in the Immediate Window and press Enter:

? ActiveWorkbook.FileFormat = xlOpenXMLWorkbook

This results in True or False. Alternatively:

? ActiveWorkbook.FileFormat = 56

Each file format has its own number and name, that you can read with the FileFormat property in VBA. In the example above we test if the value for the FileFormat property for the active workbook coincides with the Excel constant xlOpenXMLWorkbook, which can be used in a short way as number 56. Question: How do you know the FileFormat constant for a certain FileFormat? Look in the handy tables below. The first table shows the constants with which a file can be saved in Excel 2007/2010/2013.

File formatExtensionFile Format nameFile Format number
Excel WorkbookxlsxxlOpenXMLWorkbook / xlWorkbookDefault51
Excel Macro-Enabled WorkbookxlsmxlOpenXMLWorkbookMacroEnabled52
Excel Binary WorkbookxlsbxlExcel1250
Excel TemplatexltxxlOpenXMLTemplate54
Excel Macro-Enabled TemplatexltmxlOpenXMLTemplateMacroEnabled53
Excel 97-2003 WorkbookxlsxlExcel856
Workbook normalxlsxlNormal / xlWorkbookNormal-4143
Excel 97-2003 TemplatexltxlTemplate / xlTemplate817
Microsoft Excel 5.0/95 Workbook xlsxlExcel539
Excel Add-InxlamxlOpenXMLAddIn55
Excel 97-2003 Add-InxlaxlAddIn / xlAddIn818
Formatted Text (Space delimited)prnxlTextPrinter36
Text (Tab delimited)txtxlTextWindows20
Current Platform TexttxtxlCurrentPlatformText-4158
Text (Macintosh)txtxlTextMac19
Text (MS-DOS)txtxlTextMSDOS21
Unicode TexttxtxlUnicodeText42
CSV (Comma delimited)csvxlCSV6
CSV (Macintosh)csvxlCSVMac22
CSV (Windows)csvxlCSVWindows23
CSV (MS-DOS)csvxlCSVMSDOS24
DIF (Data Interchange Format)difxlDIF9
SYLK (Symbolic Link)slkxlSYLK2 / 10
PDFpdfxlTypePDF(*)
XPS DocumentxpsxlTypeXPS(**)
OpenDocument SpreadsheetodsxlOpenDocumentSpreadsheet60
XML Spreadsheet 2003xmlxlXMLSpreadsheet46
XML DataxmlxlXMLSpreadsheet46
Single File Web Pagemht / mhtmlxlWebArchive45
Web Pagehtm / htmlxlHtml44
(*) use: ExportAsFixedFormat with Type = xlTypePDF (constant 0)
(**) use: ExportAsFixedFormat with Type = xlTypeXPS (constant 1)

For example, use in a macro procedure:

ActiveWorkbook.SaveAs FileName:="C:\MyFile", FileFormat:=xlOpenXMLWorkbook

Excel saves the active workbook as MyFile.xlsx on your C-drive. You do not need to add .xlsx yourself. With the shorthand notation 56:

ActiveWorkbook.SaveAs FileName:="C:\MyFile", FileFormat:=56

To save the active workbook as PDF or XPS:

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\MyFile"
'or
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypeXPS, FileName:="C:\MyFile"

For those of you who prefer short and concise code (or, who are lazy):

ActiveWorkbook.ExportAsFixedFormat 0, "C:\MyFile"
'or
ActiveWorkbook.ExportAsFixedFormat 1, "C:\MyFile"

Instead of the active workbook, any (valid) reference to a workbook is possible. Next to the File Format names and numbers above, there are still a number of others. As far as I know, however, you cannot save a workbook under these additional file formats in Excel 2007/2010/2013, but you can open such file formats in Excel and subsequently ask the type of file (see above for code examples). The last table below demonstrates these file formats; oftentimes these are older, deprecated, file formats.

File formatFile Format nameFile Format number
DBF2xlDBF27
DBF3xlDBF38
DBF4xlDBF411
Excel2xlExcel216
Excel2 FarEastxlExcel2FarEast27
Excel3xlExcel329
Excel4xlExcel433
Excel4 WorkbookxlExcel4Workbook35
Excel7xlExcel739
Excel9795xlExcel979543
International Add-InxlIntlAddIn26
International MacroxlIntlMacro25
WJ2WD1xlWJ2WD114
WJ3xlWJ340
WJ3FJ3xlWJ3FJ341
WK1xlWK15
WK1ALLxlWK1ALL31
WK1FMTxlWK1FMT30
WK3xlWK315
WK3FM3xlWK3FM332
WK4xlWK438
WorksheetxlWKS4
Works2 FarEastxlWorks2FarEast28
WQ1xlWQ134

Finishing up

To end this blod article, here is a code snippet that I used to save a workbook under different file formats. That way, I could assess what works in Excel 2007/2010/2013 and what does not work.

Sub InvestigateFileFormat()
' Wim Gielis ' http://www.wimgielis.com
''''' ' Looping through file formats ' 22/12/2013 '''''
On Error Resume Next Application.DisplayAlerts = False For i = 1 To 60 With Workbooks.Add .SaveAs Filename:="C:\" & Format(i, "00"), FileFormat:=i .Close End With Next Application.DisplayAlerts = True
End Sub

Right, this blog article does not prove to be the mose amusing article on my website, but it is a handy overview article. Me myself, I regularly consult this page.




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links