Hi,
About the recalculation issue, the file you linked shows fine here with the import options you mentioned. Is this the actual file you are importing, or has it been saved by Excel first? If it was modified by Excel, then it might have had recalculated it, and this could be the reason I can't see this here.
Normally the files with #null are created by 3rd party tools which don't recalculate, as it is the case of AdvExcelIO. But in a file saved by Excel, I've never seen a problem loading formula results. (and I've seen thousands of files).
About the formula names, the localized name doesn't matter, as it isn't saved in the file at all. Formulas in an xls file are saved in binary RPN notation. To make this example clearer (or maybe to make t more complex) the formula =VLOOKUP(1;A$4:B$6;2;0)'; would be stored as this byte array:
00 00 00 00 3E 00 02 00 2A 00 00 00 FF FF 00 00 FA 0F 2D FC 16 00 1E 01 00 25 03 00 05 00 00 40 01 40 1E 02 00 1E 00 00 42 04 66 00
Where the red "66" is the hexadecimal identifier for VLOOKUP. When you open this in an English Excel, it will see the "66" and show "VLOOKUP" While the German version will show "SVERWEIS" and the spanish version "BUSCARV". The same way, when you write "=SVERWEIS" in a german Excel, it will convert it to 66, and will save 66 in the xls file. The localized name isn't stored anywhere in the file, and this allows that any Excel can open it, without having to know all the localized names to convert. (As a side note, in xlsx, the names are stored instead of a byte array, but the names stored in the file are always the english names, it would be a nightmare if every localized Excel saved different names to the file).
What is happening when you export to xls is that we parse the names, and when we find the word "VLOOKUP" we write "66". Also when we read 66, we show "VLOOKUP". No conversion is going on.
You can if you really want change the names we use: The names are stored in the file:
XlsFunctionNames.inc
If you edit this file, and change this line:
(Index: 102 ; Name: 'VLOOKUP'; MinArgCount: 3 ; MaxArgCount: 4; Volatile: False; ReturnType: fmValue; ParamType:'VRRV'),
to
(Index: 102 ; Name:'SVERWEIS'; MinArgCount: 3 ; MaxArgCount: 4; Volatile: False; ReturnType: fmValue; ParamType:'VRRV'),
And you'll have localized names. (note that the function index 102 = hex(66))
Now, coming back to the bytes that get stored for the vlookup formula above, not all of those bytes are for the RPN definition of the formula. There are 8 bytes on there which contain a double with the formula result (or a pointer to a string if the formula result is a string). If you are getting #null, this is because those 8 bytes are saved to the null identifier. We don't really parse the formula or do anything with it to show the result, we just show what is in those 8 bytes. If we are importing a null, then it is because a null is saved in those bytes.
Can you recheck with the file you sent and see if it shows null? If it does, can you send me the full code you are using?