Referring to Cells in Excel by Using Index Numbers

smatijev

Has anybody managed to refer to cells in Excel in any way other than by Range (A1 notation)? To be precise, I'm interested in referring to cells using index number. I tried to use

WorkSheet.Cells(2, 3).Value := SomeValue

to put SomeValue into cell B3 as it is stated in VB help but id doesn't work in Navision because Cells doesn't take any arguments.
Also, any idea how to add worksheet so that it is the last sheet? I looked in help and it says

expression.Add(Before, After, Count, Type)

but it doesn't help much.
Thanks

jesam

In Tools/Options/General you can set the option R1C1 Reference style checked, which changes to column names to numbers.
I do however not know if this makes it possible to refer to cells through numbers in VB script ...

Jens

smatijev

smatijev wrote:

Also, any idea how to add worksheet so that it is the last sheet? I looked in help and it says

expression.Add(Before, After, Count, Type)

but it doesn't help much.

I managaed way to do this is by creating macro in template and then calling it from Navision.

ajhvdb

Very interesting, could you show an example of the code?

janpieter

Maybe a little late for an answer but i think you have to do this:

VAR:
Range : 'Microsoft Excel 11.0 Object Library'.Range
App : 'Microsoft Excel 11.0 Object Library'.Application
Workssheet : 'Microsoft Excel 11.0 Object Library'.Worksheet
nCount : integer
// -----------------

CREATE(App); // you probably already did that ...

// your 2nd problem
nCount := App.Worksheets.Count;
Worksheet := App.Worksheets.Add(0,nCount);

// your 1st problem
Range := WorkSheet.Cells.Item(2,3);
Range.Value := SomeValue;

I did not test it, but with this code i think you are close. The only thing i'm not sure of if the worksheets.add works with that 0. Play a little with it. Its an optional paramter but navision does require you to give something.

With your first problem you forgot Item. In VB this is the default property so in VB it is not required. Navision does not know such thing as default property so you have to give the name of the default property (item).

janpieter

...

MrCul1

To Your first problem - I think the simplest way is something like this:

xlWorkSheet.Range('A1').Value := 'Text';

hojin

=D> You reply became many help in me.
Thanks~