Excel 2007 and the lost ‘xlFileFormat.xlXMLData’ Enum

In Excel 2007, the xlFileFormat enum called xlXMLData with value 47 that was introduced with Excel 2003, has disappeared.

The xlFileFormat enum can be used with the .SaveAs method to save a file to a file format different than the actual workbook, for instance if you need to save to an older or newer format.

These are the current enums I was able to trace for the xlFileFormat enum, from Excel 2000 up to Excel 2007 which should also cover Excel 2010 (according to this page: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx).

In Bold the first appearance after Excel 2000, in Red the ones that were introduced but are now missing.

Excel 2000	Excel 2002/XP	Excel 2003	Excel 2007 (and Excel 2010)	Value	Description
xlAddIn	xlAddIn	xlAddIn	xlAddIn	18	Microsoft Excel 97-2003 Add-In
			xlAddIn8	18	Microsoft Excel 97-2003 Add-In
xlCSV	xlCSV	xlCSV	xlCSV	6	CSV
xlCSVMac	xlCSVMac	xlCSVMac	xlCSVMac	22	Macintosh CSV
xlCSVMSDOS	xlCSVMSDOS	xlCSVMSDOS	xlCSVMSDOS	24	MSDOS CSV
xlCSVWindows	xlCSVWindows	xlCSVWindows	xlCSVWindows	23	Windows CSV
xlCurrentPlatformText	xlCurrentPlatformText	xlCurrentPlatformText	xlCurrentPlatformText	-4158	Current Platform Text
xlDBF2	xlDBF2	xlDBF2	xlDBF2	7	DBF2
xlDBF3	xlDBF3	xlDBF3	xlDBF3	8	DBF3
xlDBF4	xlDBF4	xlDBF4	xlDBF4	11	DBF4
xlDIF	xlDIF	xlDIF	xlDIF	9	DIF
			xlExcel12	50	Excel12
xlExcel2	xlExcel2	xlExcel2	xlExcel2	16	Excel2
xlExcel2FarEast	xlExcel2FarEast	xlExcel2FarEast	xlExcel2FarEast	27	Excel2 FarEast
xlExcel3	xlExcel3	xlExcel3	xlExcel3	29	Excel3
xlExcel4	xlExcel4	xlExcel4	xlExcel4	33	Excel4
xlExcel4Workbook	xlExcel4Workbook	xlExcel4Workbook	xlExcel4Workbook	35	Excel4 Workbook
xlExcel5	xlExcel5	xlExcel5	xlExcel5	39	Excel5
xlExcel7	xlExcel7	xlExcel7	xlExcel7	39	Excel7
			xlExcel8	56	Excel8
xlExcel9795	xlExcel9795	xlExcel9795	xlExcel9795	43	Excel9795
xlHTML	xlHtml	xlHtml	xlHtml	44	HTML format
xlIntlAddIn	xlIntlAddIn	xlIntlAddIn	xlIntlAddIn	26	International Add-In
xlIntlMacro	xlIntlMacro	xlIntlMacro	xlIntlMacro	25	International Macro
			xlOpenDocumentSpreadsheet	60	OpenDocument Spreadsheet
			xlOpenXMLAddIn	55	Open XML Add-In
			xlOpenXMLTemplate	54	Open XML Template
			xlOpenXMLTemplateMacroEnabled	53	Open XML Template Macro Enabled
			xlOpenXMLWorkbook	51	Open XML Workbook
			xlOpenXMLWorkbookMacroEnabled	52	Open XML Workbook Macro Enabled
xlSYLK	xlSYLK	xlSYLK	xlSYLK	2	SYLK
xlTemplate	xlTemplate	xlTemplate	xlTemplate	17	Template
			xlTemplate8	17	Template 8
xlTextMac	xlTextMac	xlTextMac	xlTextMac	19	Macintosh Text
xlTextMSDOS	xlTextMSDOS	xlTextMSDOS	xlTextMSDOS	21	MSDOS Text
xlTextPrinter	xlTextPrinter	xlTextPrinter	xlTextPrinter	36	Printer Text
xlTextWindows	xlTextWindows	xlTextWindows	xlTextWindows	20	Windows Text
xlUnicodeText	xlUnicodeText	xlUnicodeText	xlUnicodeText	42	Unicode Text
	xlWebArchive	xlWebArchive	xlWebArchive	45	Web Archive
xlWJ2WD1	xlWJ2WD1	xlWJ2WD1	xlWJ2WD1	14	WJ2WD1
xlWJ3	xlWJ3	xlWJ3	xlWJ3	40	WJ3
xlWJ3FJ3	xlWJ3FJ3	xlWJ3FJ3	xlWJ3FJ3	41	WJ3FJ3
xlWK1	xlWK1	xlWK1	xlWK1	5	WK1
xlWK1ALL	xlWK1ALL	xlWK1ALL	xlWK1ALL	31	WK1ALL
xlWK1FMT	xlWK1FMT	xlWK1FMT	xlWK1FMT	30	WK1FMT
xlWK3	xlWK3	xlWK3	xlWK3	15	WK3
xlWK3FM3	xlWK3FM3	xlWK3FM3	xlWK3FM3	32	WK3FM3
xlWK4	xlWK4	xlWK4	xlWK4	38	WK4
xlWKS	xlWKS	xlWKS	xlWKS	4	Worksheet
			xlWorkbookDefault	51	Workbook default
xlWorkbookNormal	xlWorkbookNormal	xlWorkbookNormal	xlWorkbookNormal	-4143	Workbook normal
xlWorks2FarEast	xlWorks2FarEast	xlWorks2FarEast	xlWorks2FarEast	28	Works2 FarEast
xlWQ1	xlWQ1	xlWQ1	xlWQ1	34	WQ1
		xlXMLData		47
	xlXMLSpreadsheet	xlXMLSpreadsheet	xlXMLSpreadsheet	46	XML Spreadsheet

Dutch.

Excel ‘Could not get the List property’ error.

If you ever bump into the ‘Could not get the List property. Not enough storage space is available to complete this operation‘ error in Excel, then you are likely to be using a ListBox either as an ActiveX control on the sheet or as a ListBox control on a UserForm, that is linked to a Range on one of your worksheets and you trying to obtain the ListBox’s .Value property (usually the default property) .

This error can occur for 2 reasons:

1. your ListBox does not have a valid .ListFillRange, i.e. empty or pointing to an invalid Range. The .ListFillRange property tells Excel the Range of a sheet in the workbook that contains the data used to populate the list
2. your ListBox contains a textual entry that is not part of the accepted values

An invalid Range occurs when for instance you delete the sheet or the range it points to. Excel is unable to make the match and returns the above error.

In my case the error came up while using an ActiveX ComboBox with 2 columns pointing to a 2 column range and hiding the first column (Setting the .ColumnWidths to ‘0 pt; 150 pt‘).

Dutch