Excel Automation - How to Refer to Cells by Using Index Numbers

How can I do the same than the VB code below with in Navision ? Sub EnterValue() Worksheets(“Sheet1”).Cells(6, 1).Value = 10 End Sub

Try this; Here xlWorksheet is Automation variable xlWorksheet.Range(‘A1’).Value:=‘Item No.’;

I knew hot to access cells and ranges by using the A1 notation but what I’m after is how to Refer to cells using index numbers because I need to loop through the X and Y axis of the spreadsheet. That’s what the online help says about it : (it works fine in VB but I don’t know how to do the same in NF, DOES ANYONE HAS THE CORRECT SYNTAX FOR ACCESSING CELL USING INDEX NUMBER???) Referring to Cells by Using Index Numbers You can use the Cells property to refer to a single cell by using row and column index numbers. This property returns a Range object that represents a single cell. In the following example, Cells(6,1) returns cell A6 on Sheet1. The Value property is then set to 10. Sub EnterValue() Worksheets(“Sheet1”).Cells(6, 1).Value = 10 End Sub The Cells property works well for looping through a range of cells, because you can substitute variables for the index numbers, as shown in the following example. Sub CycleThrough() Dim counter As Integer For counter = 1 To 20 Worksheets(“Sheet1”).Cells(counter, 3).Value = counter Next counter End Sub Note If you want to change the properties of or apply a method to a range of cells all at once, use the Range property. For more information, see Referring to cells using A1 notation.

If you use the “xlsCell” function to translate numeric x-y cell references into the Letter-Row coordinate (–> such as col 5, Row 23 would be translated: xlsCell(5,23) → ‘E23’) you can still loop through your worksheet. Example: For row := 1 to 10 Do For col := 5 to 8 do xlsDecimal(row,col,aDecValue); end; end; You find these functions I mentioned in my post titled “Tips&Tricks: Excel Automation” in this forum. I think it should also be possible to access a range directly but the syntax is quite complicated. Something like ‘R[23]C[5]’. The reason why I don’t like this kind of referencing is that the application is not language independend. For the english version of Excel, R[23]C[5] is correct as R=row and C=Col. But for the German version you’d have to write Z[23]S[]5; where Z=Zeile=Row and S=Spalte=Col. I guess this is true for all non-english Excel versions. On the other hand, a Cell named E23 is the same in all language versions. Marcus Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch

Hi Tarek try this function Pass the Column and get bact the text Value It’s part of my Excel report from www.mynavision.net Function = GetColumnStr(ColunmNo : Integer) : Code[2] ColumnStr:=’’; IF ColunmNo <=0 THEN EXIT(ColumnStr); ColunmNo:=ColunmNo + 25; ModInt:=ColunmNo MOD 26; // Defaults TO 0 DivInt :=ColunmNo DIV 26; // Min 1 IF (DivInt > 1) THEN ColumnStr:=COPYSTR(’ ABCDEFGHIJKLMNOPQRSTUVWXYZ’,DivInt,1); ColumnStr:=ColumnStr+COPYSTR(‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’,ModInt+1,1); EXIT(ColumnStr); Can be called from this function >> Function InsetCell(CellStr : Text[120];CellColumn : Integer;CellRow : Integer;SetBold : Boolean;Decimal : Boolean;Places : Integer) Sheet.Range(GetColumnStr(CellColumn) + FORMAT(CellRow)).Font.Bold := SetBold; Sheet.Range(GetColumnStr(CellColumn) + FORMAT(CellRow)).Value := CellStr; IF (Decimal) AND (CellStr <> ‘* ERROR *’) THEN CASE Places OF 0: Sheet.Range(GetColumnStr(CellColumn) + FORMAT(CellRow)).NumberFormat := ‘###,###,##0_);Red’; 1: Sheet.Range(GetColumnStr(CellColumn) + FORMAT(CellRow)).NumberFormat := ‘###,###,##0.0_);Red’; 2: Sheet.Range(GetColumnStr(CellColumn) + FORMAT(CellRow)).NumberFormat := ‘###,###,##0.00_);Red’; 3: Sheet.Range(GetColumnStr(CellColumn) + FORMAT(CellRow)).NumberFormat := ‘###,###,##0.000_);Red’; 4: Sheet.Range(GetColumnStr(CellColumn) + FORMAT(CellRow)).NumberFormat := ‘###,###,##0.0000_);Red’; 5: Sheet.Range(GetColumnStr(CellColumn) + FORMAT(CellRow)).NumberFormat := ‘###,###,##0.00000_);Red’; END; Mr David Cox MindSource (UK) Limited Navision Solutions Partner Email: david@mindsource.co.uk Web: www.mindsource.co.uk

Hi Tarek, Maybe this piece of code using the OffSet function can help you. … CREATE(l_autApp); l_autApp.Visible(TRUE); l_autBook := l_autApp.Workbooks.Add; l_autSheet := l_autBook.ActiveSheet; l_autSheet.Range(‘A1’).Offset(2,0).Value := ‘Group’; l_autSheet.Range(‘A1’).Offset(2,1).Value := ‘Code’; l_autSheet.Range(‘A1’).Offset(2,2).Value := ‘EAN’; l_autSheet.Range(‘A1’).Offset(2,3).Value := ‘Description’; … ------------------

You can call a funtion as: CompruebaColumna(Columna) as xColumna CASE Columna OF 1: xColumna := ‘A’; 2: xColumna := ‘B’; 3: xColumna := ‘C’; 4: xColumna := ‘D’; 5: xColumna := ‘E’; 6: xColumna := ‘F’; 7: xColumna := ‘G’; 8: xColumna := ‘H’; 9: xColumna := ‘I’; 10: xColumna := ‘J’; 11: xColumna := ‘K’; 12: xColumna := ‘L’; 13: xColumna := ‘M’; 14: xColumna := ‘N’; 15: xColumna := ‘O’; 16: xColumna := ‘P’; 17: xColumna := ‘Q’; 18: xColumna := ‘R’; 19: xColumna := ‘S’; 20: xColumna := ‘T’; 21: xColumna := ‘U’; 22: xColumna := ‘V’; 23: xColumna := ‘W’; 24: xColumna := ‘X’; 25: xColumna := ‘Y’; 26: xColumna := ‘Z’; 27: xColumna := ‘AA’; 28: xColumna := ‘AB’; 30: xColumna := ‘AC’; 31: xColumna := ‘AD’; 32: xColumna := ‘AE’; 33: xColumna := ‘AF’; 34: xColumna := ‘AG’; 35: xColumna := ‘AH’; 36: xColumna := ‘AI’; 37: xColumna := ‘AJ’; 38: xColumna := ‘AK’; 39: xColumna := ‘AL’; 40: xColumna := ‘AM’; 41: xColumna := ‘AN’; 42: xColumna := ‘AO’; 43: xColumna := ‘AP’; 44: xColumna := ‘AQ’; 45: xColumna := ‘AR’; 46: xColumna := ‘AS’; 47: xColumna := ‘AT’; 48: xColumna := ‘AU’; 49: xColumna := ‘AV’; 50: xColumna := ‘AW’; 51: xColumna := ‘AX’; 52: xColumna := ‘AY’; 53: xColumna := ‘AZ’; END;

quote:


Originally posted by Agustin Carceller: CompruebaColumna(Columna) as xColumna CASE Columna OF 1: xColumna := ‘A’; 2: xColumna := ‘B’; 3: xColumna := ‘C’; 4: xColumna := ‘D’; 5: xColumna := ‘E’; … 30: xColumna := ‘AC’; 31: xColumna := ‘AD’; 32: xColumna := ‘AE’; END;


Oh, my God, what kind of programming style is that? What about Column 127 or 378? Instead of a Mega-CASE I would recommend a small smart function like this:

Function stColumn (column : Integer) : Text[2];
Begin
// Column 5 --> "E"
// Column 28,  --> "AB"

IF Column > 26 THEN BEGIN
  ColumnFirst := Column DIV 26;
  Column := Column MOD 26;
END
ELSE
  ColumnFirst := 0;
Letters := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
IF ColumnFirst <> 0 THEN
  EXIT (STRSUBSTNO('%1%2',Letters[ColumnFirst],Letters[Column]))
ELSE
  EXIT (STRSUBSTNO('%1',Letters[Column]));
End;
 

Marcus Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch

Sorry to insist, but if the original question is about refering to cells using numbers, I think you should try the Offset function. Example: CREATE(l_autApp); l_autApp.Visible(TRUE); l_autBook := l_autApp.Workbooks.Add; l_autSheet := l_autBook.ActiveSheet; Customer.FIND(’-’); RowNo := 0; REPEAT l_autSheet.Range(‘A1’).Offset(RowNo,0).Value := Customer.Code; l_autSheet.Range(‘A1’).Offset(RowNo,1).Value := Customer.Name; RowNo := RownNo + 1; UNTIL Customer.NEXT = 0 Edited by - jsoage on 2001 Mar 05 20:13:42

Moved from “Attain/Financials Developer Forum” to “Attain/Financials Developer FAQ”.