Jump to content

How to know the latest cell number IN EXCEL LISP


hosneyalaa

Recommended Posts

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)
)

 

 

Capture01.PNG.9f6a08b98a7a1f938fbb5558b539c2c1.png

Link to comment
Share on other sites

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

  • Thanks 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...