File formats
- Dec. 22, 2013
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 format | Extension | Description |
| Excel Workbook | xlsx | Standard file format in Excel 2007/2010/2013, based on XML. No macro procedures! |
| Excel Macro-Enabled Workbook | xlsm | file format in Excel 2007/2010/2013, based on XML. Allows macro procedures toe. |
| Excel Binary Workbook | xlsb | Binary file format in Excel 2007/2010/2013. |
| Excel Template | xltx | Standard file format in Excel 2007/2010/2013 for a Template. No macro procedures! |
| Excel Macro-Enabled Template | xltm | file format in Excel 2007/2010/2013 for a Template. Allows macro procedures toe. |
| Excel 97-2003 Workbook | xls | Excel 97-2003 binary file format |
| Excel 97-2003 Template | xlt | Excel 97-2003 binary file format for a Template |
| Microsoft Excel 5.0/95 Workbook | xls | Excel 5.0/95 binary file format |
| Excel Add-In | xlam | Standard file format in Excel 2007/2010/2013, based on XML, ffor an Add-In. |
| Excel 97-2003 Add-In | xla | Excel 97-2003 binary file format for an Add-In |
| Text format | Extension | Description |
| Formatted Text (Space delimited) | prn | Saves as an space-delimited file format |
| Text (Tab delimited) | txt | Saves as a tab-delimited text file for Windows (**) |
| Text (Macintosh) | txt | Saves as an tab-delimited text file for Macintosh (**) |
| Text (MS-DOS) | txt | Saves as an tab-delimited text file for MS-DOS (**) |
| Unicode Text | txt | Saves as Unicode text (*) |
| CSV (Comma delimited) | csv | Saves as a comma-delimited text file for Windows (**) |
| CSV (Macintosh) | csv | Saves as a comma-delimited text file for Macintosh (**) |
| CSV (MS-DOS) | csv | Saves as a comma-delimited text file for MS-DOS (**) |
| DIF (Data Interchange Format) | dif | Saves as the Data Interchange file format |
| SYLK (Symbolic Link) | slk | Saves as the Symbolic Link file format |
(**): Tabs, linebreaks and other characters are interpret correctly.
| Other formats | Extension | Description |
| PDF is a file format with a fixed layout that allows a file to retain its intended layout when it is viewed or printed | ||
| XPS Document | xps | XPS has the same objective as PDF, yet it is a different file format |
| OpenDocument Spreadsheet | ods | The OpenDocument Spreadsheet file format is a version of the OpenDocument Format (ODF) v1.1 |
| XML Spreadsheet 2003 | xml | The XML Spreadsheet 2003 file format |
| XML Data | xml | The XML Data file format |
| Single File Web Page | mht / mhtml | Saves (part of) a file as a Web page, including the underlying files and folders |
| Web Page | htm / html | Saves (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 format | Extension | File Format name | File Format number |
| Excel Workbook | xlsx | xlOpenXMLWorkbook / xlWorkbookDefault | 51 |
| Excel Macro-Enabled Workbook | xlsm | xlOpenXMLWorkbookMacroEnabled | 52 |
| Excel Binary Workbook | xlsb | xlExcel12 | 50 |
| Excel Template | xltx | xlOpenXMLTemplate | 54 |
| Excel Macro-Enabled Template | xltm | xlOpenXMLTemplateMacroEnabled | 53 |
| Excel 97-2003 Workbook | xls | xlExcel8 | 56 |
| Workbook normal | xls | xlNormal / xlWorkbookNormal | -4143 |
| Excel 97-2003 Template | xlt | xlTemplate / xlTemplate8 | 17 |
| Microsoft Excel 5.0/95 Workbook | xls | xlExcel5 | 39 |
| Excel Add-In | xlam | xlOpenXMLAddIn | 55 |
| Excel 97-2003 Add-In | xla | xlAddIn / xlAddIn8 | 18 |
| Formatted Text (Space delimited) | prn | xlTextPrinter | 36 |
| Text (Tab delimited) | txt | xlTextWindows | 20 |
| Current Platform Text | txt | xlCurrentPlatformText | -4158 |
| Text (Macintosh) | txt | xlTextMac | 19 |
| Text (MS-DOS) | txt | xlTextMSDOS | 21 |
| Unicode Text | txt | xlUnicodeText | 42 |
| CSV (Comma delimited) | csv | xlCSV | 6 |
| CSV (Macintosh) | csv | xlCSVMac | 22 |
| CSV (Windows) | csv | xlCSVWindows | 23 |
| CSV (MS-DOS) | csv | xlCSVMSDOS | 24 |
| DIF (Data Interchange Format) | dif | xlDIF | 9 |
| SYLK (Symbolic Link) | slk | xlSYLK | 2 / 10 |
| xlTypePDF | (*) | ||
| XPS Document | xps | xlTypeXPS | (**) |
| OpenDocument Spreadsheet | ods | xlOpenDocumentSpreadsheet | 60 |
| XML Spreadsheet 2003 | xml | xlXMLSpreadsheet | 46 |
| XML Data | xml | xlXMLSpreadsheet | 46 |
| Single File Web Page | mht / mhtml | xlWebArchive | 45 |
| Web Page | htm / html | xlHtml | 44 |
(**) 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 format | File Format name | File Format number |
| DBF2 | xlDBF2 | 7 |
| DBF3 | xlDBF3 | 8 |
| DBF4 | xlDBF4 | 11 |
| Excel2 | xlExcel2 | 16 |
| Excel2 FarEast | xlExcel2FarEast | 27 |
| Excel3 | xlExcel3 | 29 |
| Excel4 | xlExcel4 | 33 |
| Excel4 Workbook | xlExcel4Workbook | 35 |
| Excel7 | xlExcel7 | 39 |
| Excel9795 | xlExcel9795 | 43 |
| International Add-In | xlIntlAddIn | 26 |
| International Macro | xlIntlMacro | 25 |
| WJ2WD1 | xlWJ2WD1 | 14 |
| WJ3 | xlWJ3 | 40 |
| WJ3FJ3 | xlWJ3FJ3 | 41 |
| WK1 | xlWK1 | 5 |
| WK1ALL | xlWK1ALL | 31 |
| WK1FMT | xlWK1FMT | 30 |
| WK3 | xlWK3 | 15 |
| WK3FM3 | xlWK3FM3 | 32 |
| WK4 | xlWK4 | 38 |
| Worksheet | xlWKS | 4 |
| Works2 FarEast | xlWorks2FarEast | 28 |
| WQ1 | xlWQ1 | 34 |
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 ' https://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 = TrueEnd 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.
