Navision changes the

ib000904

I'm trying to create a report from file 32 (Item ledger entries) which, as a sort of hidden feature, creates a subtotal of all the processed 'quantities' in a cell in an existing spreadsheet.

Apart from the usual declaration-commands to activate excel, i added some commands in the onaftergetrecord() section:
X:=xlsheet.Range('B2').Value; // read Cell B2 into variable 'X
X+="Item Ledger Entry".Quantity; // Add quantity-value to 'X'
xlsheet.Range('B2').Value:=X; // write X back into cell B2.

Strange thing is, if i initially fill Cell B2 with '-1,00' with no currency format whatsoever, right after the first record has passed, cell B2 is filled with something like € 98,00- (quantity having been -97).
In the second pass this '€ 98,00-' is obviously suddenly seen as text, because an error message appears about trying to convert text into a decimal-variable.
How do i fix cell B2 to it's initial format without currency?

jlandeen

It looks like you're trying to write your own custom logic using the Office Automation objects. I have 2 suggestions:
1) take a look at Table 370 - Excell Buffer (as it has code in there to handle writing Excel data via the Automation dlls)
2) Don't write any automation code yourself - just use the Excel Buffer table and it's functionality to write to Excel. This will allow you to focus on writing Navision code and let it handle the Excel/Automation logic.

ib000904

The standard Navision feature with table 370 only allows you to WRITE data to an Excel file. Not to interact with Excel. I want to read something from Excel, compare that to something that's in Navision, do some calculation and then write the result back to Excel. Easy as that.
Can't achieve that using the mentioned Navision-Excel features for that.
To be honest, why not use the full potential of the MS-environment, if the system allows you to?

jlandeen

Don't get me wrong - I'm all for using external COM/Active X objects and components in Navision. I just like to reuse whatever I can before I look to building new things.

Have you tried using the variant datatype? As some controls/automation objects use variants instead of explicit data types it can be easier to use that variable type and then handle any conversion logic youself (i.e. stripout currency symbols).