Determining what version of MS Excel was used to create BEx workbook

There are few of limitations when it comes to usage of BEx Query Designer within particular version of MS Excel. Mostly they are related to the fact that workbooks created in one version of MS Excel may do not work in other version. E.g. the workbooks created with MS Excel 2007 having format xlsx, xlsm or xlsb can’t be opened with Excel 2010. For more details about similar issues refer to Notes:

1498919 - Known Limitations of BExAnalyzer with Excel 2010

1175947 - Known Limitations of Analyzer with Excel 2007

Therefore fundamental question in issues like user is complaining that the workbook that worked in one version of the Excel doesn’t work in other is to check in what version of the Excel the workbook was created.

One way to find out that would be to have a look to extension of the Excel file. Up to version 2017 the Excel used only proprietary binary format – extension XLS. Afterwards it also uses Office Open XML as its primary file format – it has extension XLSM (or XLSX). These files are just a ZIP files with folder structure and several XML files. One of the XML files has in information about the version which was used to produce the file. The XLSM file just needs to be temporarily renamed to ZIP and in the folder called docProps a file called app.xml has to be displayed. In the XML element called the version is stored.

Now how to match the number from file (14 in this case) with the Excel version. Here below table (from wikipedia) becomes handy. In our case 14 means Excel 2010:

Excel 3          3.0    

Excel 4          4.0    

Excel 5          5.0    

Excel 95        7.0    

Excel 97        8.0    

Excel 2000     9.0    

Excel 2002     10.0

Excel 2003     11.0

Excel 2007     12.0

Excel 2010     14.0

Excel 2013     15.0

Excel 2016     16.0

In case one would try to use e.g. BEx workbook created in MS 2013 (version 15 would be stored in the app.xml file) in Excel of version 2010 (version 14) most likely it would not be possible as there would be libraries used by macros written in the Visual Basic incompatible between different versions of MS Excels. Attempt to open the Excel file would be ending up with errors like:

Error in loading DLL

or

Workbook corrupted error

The issue of missing or incompatible libraries can be spotted in editor of Microsoft Visual Basic for Application. Where in menu called Tools -> References a missing library would be highlighted like below:

More information:

1581104 - Workbooks created in Excel2007 cannot be opened in Excel2010