hosneyalaa Posted July 15, 2019 Share Posted July 15, 2019 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) ) Quote Link to comment Share on other sites More sharing options...
hosneyalaa Posted July 15, 2019 Author Share Posted July 15, 2019 THIS FILE EXCEL 02010102.xls Quote Link to comment Share on other sites More sharing options...
hosneyalaa Posted July 22, 2019 Author Share Posted July 22, 2019 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 1 Quote Link to comment Share on other sites More sharing options...
BIGAL Posted July 23, 2019 Share Posted July 23, 2019 Admin posted twice. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
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.