How to know the latest cell number IN EXCEL LISP

[hosneyalaa]

How to know the latest cell number

Only in the column  A

As it is shown in the picture

 

Where it is in the language VBA EXCEL
The variable ( LD )  gives another cell number containing data in the column A

Dim LD As Long
LD = Range("A" & Rows.Count).End(xlUp).Row

 

You can help with this idea in  fixo LISP

THANK YOU

 

 fixo LISP

(defun C:DEMO (/ Bord ExcelApp FilePath Fonto Rang Sel Sht ShtNum Wbk)
(vl-load-com)

(setq FilePath (getfiled "Select Excel file to read :"
(getvar "dwgprefix")
"xls"
16
)
)
(setq ShtNum (getint "\nEnter sheet number <1>: "))
(if (not ShtNum)(setq ShtNum 1))

(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true);do not set to :vlax-false for invisible mode
(setq Wbk (vl-catch-all-apply 'vla-open
(list (vlax-get-property ExcelApp "WorkBooks") FilePath)))
(vlax-invoke-method Wbk "Activate") 
(setq Sht (vl-catch-all-apply 'vlax-get-property
(list (vlax-get-property Wbk "Sheets")
"Item" ShtNum)))
(vlax-invoke-method Sht "Activate")
;;Find last cell :
(vlax-invoke-method ExcelApp "Volatile")
(setq FindRang (vlax-get-property Sht "UsedRange"))
(setq RowNum (vlax-get-property
(vlax-get-property
FindRang "Rows") "Count"))
(setq lastRow (vlax-variant-value
(vlax-get-property (vlax-get-property
FindRang "Rows") "Item" RowNum)))
(setq lastCell (vlax-get-property lastRow "End" 2))
;;working with columns :
(setq Rang (vlax-variant-value
(vlax-get-property (vlax-get-property Sht "Columns")
"Item" 1))); 1 is column number
(vlax-put-property Rang "ColumnWidth" (vlax-make-variant 25.0 3)); 25.0 is new column width, 3 is variant type 
(vlax-put-property Rang "Horizontalalignment" (vlax-make-variant -4108 3))
;;working with rows :
;;; (setq Rang (vlax-get-property Sht "Range" "A:A"));get first row
;; the same as :
(setq Rang (vlax-variant-value
(vlax-get-property (vlax-get-property Sht "Rows")
"Item" 1))); 1 is row number
(vlax-put-property Rang "RowHeight" (vlax-make-variant 18.0 3)); 25.0 is new row height, 3 is variant type 
(vlax-put-property Rang "Horizontalalignment" (vlax-make-variant -4108 3))
(vlax-put-property (vlax-get-property Rang "Interior")
"Colorindex" (vlax-make-variant 4))
;;working with borders :
(setq Bord (vlax-get-property Rang "Borders"))
(vlax-put-property Bord "Color" (vlax-make-variant -1 3)) ; borders off
(vlax-put-property Bord "Color" (vlax-make-variant 1 3)) ;borders on
;; border lines (thin)
(vlax-put-property Bord "LineStyle" (vlax-make-variant 1 3))
;; borders color
(vlax-put-property Bord "Colorindex" (vlax-make-variant 5))
;;working with font :
(setq Fonto (vlax-get-property Rang "Font"))
(vlax-put-property Fonto "Name" (vlax-make-variant "Times New Roman" 12))
(vlax-put-property Fonto "Size" (vlax-make-variant 12 5))
(vlax-put-property Fonto "Bold" (vlax-make-variant 1 11))
(vlax-put-property Fonto "Italic" (vlax-make-variant 1 11))
(vlax-put-property Fonto "Colorindex" (vlax-make-variant 5));ETC

; continuing the code above
;; working with separate cell :
(vlax-invoke-method Rang "Activate");optional
(setq Cels (vlax-get-property Rang "Cells"))
(setq R1 1
C1 1)
(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))
(vlax-put-property Cel "Value2" (vlax-make-variant "First Cell in Range" 12))
;; Set interior color :
(vlax-put-property (vlax-get-property Cel "Interior")
"Colorindex" (vlax-make-variant 28))
;; select the particular cell:
(vlax-invoke-method Cel "Select")
;; get application selection: 
(setq Sel (vlax-get-property ExcelApp "Selection"))
;; get selection borders
(setq Bords (vlax-get-property Sel "Borders"))
;; iterate through all edges of selection
(setq cnt 0)
(vlax-for a Bords
(setq cnt (1+ cnt))
(vl-catch-all-apply (function (lambda()
(progn 
(if (< cnt 5)
(progn 
(vlax-put-property a "LineStyle"
(vlax-make-variant 1 3))
(vlax-put-property a "Weight"
(vlax-make-variant 4 3))
(vlax-put-property a "ColorIndex"
(vlax-make-variant 1 5)));progn
;; turn off the diagonal lines:
(vlax-put-property a "LineStyle" (vlax-make-variant -4142 3))
))))))
;; Horizontal alignment Center :
(vlax-put-property Cel "Horizontalalignment" (vlax-make-variant -4108 3))
;; Vertical alignment Bottom :
(vlax-put-property Cel "VerticalAlignment" (vlax-make-variant -4107 3))
;; Set number format :
(vlax-put-property Cel "NumberFormat" (vlax-make-variant "0,00" )
(setq R1 1
C1 2)
(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))
;; get cell value :
(setq cval (vlax-variant-value (vlax-get-property Cel "Value")))
;; Horizontal alignment Left(Indent) :
(vlax-put-property Cel "Horizontalalignment" (vlax-make-variant -4131 3))
;; Vertical alignment Center :
(vlax-put-property Cel "VerticalAlignment" (vlax-make-variant -4108 3))
;; Set text format :
(vlax-put-property Cel "NumberFormat" (vlax-make-variant "@" )
;; ETC
;;; (vl-catch-all-apply
;;; 'vlax-invoke-method
;;; (list Wbk "Close")
;;; );close file w/o saving of changes

;; *** or if you need to save changes :

;;;(vlax-invoke-method
;;;Wbk
;;;'SaveAs
;;;(vlax-get-property wbk "Name");short name
;;;-4143 ;exel file format (excel constant)
;;;nil
;;;nil
;;;:vlax-false
;;;:vlax-false
;;;1
;;;2
;;
;;; (vl-catch-all-apply
;;; 'vlax-invoke-method
;;; (list Wbk "Close" )
;;; )
;; **** 
;;; (vl-catch-all-apply
;;; 'vlax-invoke-method
;;; (list ExcelApp "Quit")
;;; )
(mapcar
(function (lambda (x)
(vl-catch-all-apply (function (lambda()
(if (not (vlax-object-released-p x))

(vlax-release-object x)
)
)
))))
(list Bord Bords Cel Fonto lastCell lastRow FindRang Rang Sel Sht Wbk ExcelApp)
)
(setq Bord nil
Bords nil
Fonto nil
Cel nil
Sel nil
Rang nil
Sht nil
Wbk nil
ExcelApp nil
)
(gc)
(gc)
(princ)
)

 

 

[hosneyalaa]

THIS FILE EXCEL 

02010102.xls

[hosneyalaa]

Praise be to God 

 I found the solution

 

 
(setq SelRng (vlax-get-property Sht 'Range "A1000"));CELL A1000
(vlax-invoke-method SelRng "Select")
(setq NextRng (vlax-get-property ExcelApp "Selection"));SELECT_RANGE
(setq DesRng (vl-catch-all-apply'vlax-get-property (list NextRng "End" -4162))); -4121 = xlUp ;GO TO UP CELLS
(SETQ lastCellE (vlax-invoke-method DesRng "Select")); SELECT

 

COUNT ROWS IN RANG LAST CELL TO A1
  (setq RangROW (vlax-get-property Sht "Range" DesRng (strcat "A" (itoa 1))));get RANG LAST CELL+A1
(vlax-invoke-method RangROW "Select")
(setq RowNum0 (vlax-get-property(vlax-get-property RangROW "Rows") "Count"));COUNT ROWS IN RANG LAST CELL TO A1

[BIGAL]

Admin posted twice.