I have to import file in the ledger column to excel in order to make some scenario’s for my company. when I take it out from MD Navision, the file type was HTML. It was going well to import the files, but when I tried to make some calculations, Excel does not see the numbers (that I had just imported from HTML) as numbers, but as an unknown type. I am stuck now with this problem for two days, can anyone help me how to solve this problem?
PS: I know I should directly convert the file to Excel in MD Navision, but the option does not work. I pressed Excel icon for many times, but nothing happens. Please your help [:(]
It’s not unknown type - it’s “Text”, thanks to Excel’s supersmart guessing what it should be. Moreover, Excel can easily convert to Date something it thinks could be a date, for example document number like this one: 07/12.
Was it a Report you “saved as HTML”? It should be, as this is the only way to save reports to file, the Send to Excel button is not applicable there, it doesn’t work with Reports by design.
This is purely Excel’s problem, and difficult to automate.
You must set cell’s format BEFORE any data are entered - otherwise already mentioned supersmart guessing kicks in. Here the problem is, as you do not know WHICH cells to format.
Another Excel’s “cool feature” [:@] is, that if it has decided the cell to be “Text” (or “Date” or whatever), it keeps to that formatting,
EVEN if you select the cell, click Format Cell and choose some other format, (in this case, General / Numeric / Currency is what you need). Excel ignores your request to change format, has been doing it for years throughout all versions including the latest.
One way to solve this is entering a formula in another cell =A1*1, given in A1 is the number, formatted as Text - clumsy & boring workaround, but I myself have had to use this - that for I avoid export2HTML + open HTML in Excel as much as possible.
I would suggest getting the Excel function to work is the quickest route to achieve what you want to.
What version of NAV is it, and is it running on a server/terminal server that has access to Excel?
The Excel button not working is likely to be either a missing setup in NAV (i.e. nobody has told NAV which program to run when the Excel button is pressed), or Excel is not available on the device that is running NAV (which can be common in terminal server environments).
If you simply get no response from Excel, it is likely to be one of the two problems above, if yuo get an error, it could mean a further missing microsoft component.