ekko Posted September 6, 2023 Share Posted September 6, 2023 (defun c:test (/ xlApp xlBook xlSheet atsheet columnD Columnall row col cellValue ) (vl-load-com) (setq xlApp (vlax-get-or-create-object "Excel.Application")) (setq xlBook (vlax-get-property xlApp 'Sheets) xlSheet (vlax-get-property xlBook 'Item "Sheet1") atsheet (vlax-invoke-method xlSheet 'Activate) ) (setq ColumnD (vlax-get-property xlSheet 'Range "D:D")) ;|(setq columnall (vlax-get-property xlSheet 'Columns)) (setq columnD (vlax-get-property (vlax-variant-value (vlax-get-property columnall 'Item 4) ) 'EntireColumn ) )|; (setq lastCell (vlax-invoke ColumnD 'Find "*" nil xlValues xlByColumns xlNext) ) (if lastCell (progn (setq row (vlax-get lastCell 'Row)) (setq col (vlax-get lastCell 'Column)) (setq cellValue (vlax-get lastCell 'Value)) (if (numberp cellValue) (setq cellValue (rtos cellValue 2 2)) ) (princ (strcat "Last non-empty cell in column D is in row " (itoa row) ", column " (itoa col) ", with value: " "\"" cellValue "\"" ) ) ) (princ "No non-empty cell found in column D") ) (vlax-release-object columnD) (vlax-release-object lastCell) (vlax-release-object xlApp) (gc) (gc) (princ) ) Hello everyone, I am trying to get the value and position of the non-empty cell in the last row of column D, but (vlax-invoke ColumnD 'Find "*" nil xlValues xlByColumns xlNext) this method does not seem to work, I have tried for a long time, but I still can't find it The reason is that I can only go to the forum to ask for help from friends As shown in the figure: D10 is its position, and its value is "last" test.xlsx Quote Link to comment Share on other sites More sharing options...
hosneyalaa Posted September 6, 2023 Share Posted September 6, 2023 See this https://www.cadtutor.net/forum/topic/68220-how-to-know-the-latest-cell-number-in-excel-lisp/ 1 Quote Link to comment Share on other sites More sharing options...
Stefan BMR Posted September 6, 2023 Share Posted September 6, 2023 Try this. I'm not sure I caught all possible errors, please test it in different scenarios. Ah yes, I took the lazy route of making sure the range value is always a 2-dimensional array. When the range is a single cell, the Value property returns its contents. (defun c:DCOL ( / variantvalue msg excel sheet range rows lst) (defun VariantValue (x / var) (cond ((not x) nil) ((eq (type x) 'safearray) (VariantValue (vlax-safearray->list x)) ) ((eq (type x) 'variant) (if (vl-catch-all-error-p (setq var (vl-catch-all-apply 'vlax-variant-value (list x))) ) nil (VariantValue var) ) ) ((listp x) (mapcar 'VariantValue x)) (T x) ) ) (if (and (setq msg "\nExcel is not open.") (setq excel (vlax-get-object "Excel.Application")) (setq msg "\nNo active sheet found.") (setq sheet (vlax-get excel "ActiveSheet")) (setq range (vlax-get sheet "UsedRange")) (setq rows (vlax-get (vlax-get range "Rows") "Count")) (setq range (vlax-get-property sheet "Range" (strcat "D1:D" (itoa rows)))) (setq msg "\nD column is empty.") (setq lst (variantValue (vlax-get-property range "value"))) ) (progn (if (listp lst) (setq lst (reverse lst)) (setq lst (list (list lst))) ) (while (and lst (not (caar lst))) (setq lst (cdr lst)) ) (if lst (progn (princ "\nD") (princ (length lst)) (princ " - ") (princ (caar lst)) ) (princ "\nD column is empty.") ) ) (if msg (princ msg)) ) (princ) ) 1 Quote Link to comment Share on other sites More sharing options...
BIGAL Posted September 7, 2023 Share Posted September 7, 2023 Another way not tested is to use Getcell and just walk down the column till you get a nil value as a safe guard, can get range of current sheet. Quote Link to comment Share on other sites More sharing options...
ekko Posted September 7, 2023 Author Share Posted September 7, 2023 @hosneyalaa @Stefan BMR The very good ideas provided by the two, I tried it, and it perfectly fulfilled my needs. But I still want to find out the cause of the error. About (setq lastCell (vlax-invoke ColumnD 'Find "*" nil xlValues xlByColumns xlNext) ) Why can’t this code achieve this function? Is my method of use wrong? Who can help me solve this problem? Quote Link to comment Share on other sites More sharing options...
ekko Posted September 7, 2023 Author Share Posted September 7, 2023 (edited) 12 hours ago, BIGAL said: Another way not tested is to use Getcell and just walk down the column till you get a nil value as a safe guard, can get range of current sheet. If every other cell is Nil value, or multiple cells are Nil value, is this method still applicable?Maybe I need to provide a cell endpoint for the loop? Edited September 7, 2023 by ekko Quote Link to comment Share on other sites More sharing options...
exceed Posted September 8, 2023 Share Posted September 8, 2023 (edited) ; Find Last Row/Column in Excel - 2023.09.08 exceed ; command ; FLRE = find last row ; FLCE = find last column (defun c:FLRE () (ex:FLRCE "R") ) (defun c:FLCE () (ex:FLRCE "C") ) (defun ex:FLRCE ( rc / xlApp xlBook xlSheet AcSheet Cells xlcols xlrows Maxcols MaxRows ColumnInput RowInput ColumnInt addr Rng finddirection whatyoufind whatyoufindrow whatyoufindcol whatyoufindtxt ) (vl-load-com) ;------------------------------------------------------------------------------- ; Number2Alpha - Converts Number into Alpha string ; Function By: Gilles Chanteau from Marseille, France ; Arguments: 1 ; Num# = Number to convert ; Syntax example: (Number2Alpha 731) = "ABC" ;------------------------------------------------------------------------------- (defun Number2Alpha (Num# / Val#) (if (< Num# 27) (chr (+ 64 Num#)) (if (= 0 (setq Val# (rem Num# 26))) (strcat (Number2Alpha (1- (/ Num# 26))) "Z") (strcat (Number2Alpha (/ Num# 26)) (chr (+ 64 Val#))) ) ) ) ; Alpha2Number - Converts Alpha string into Number ; Function By: Gilles Chanteau from Marseille, France ; Arguments: 1 ; Str$ = String to convert ; Syntax example: (Alpha2Number "ABC") = 731 ;------------------------------------------------------------------------------- (defun Alpha2Number (Str$ / Num#) (if (= 0 (setq Num# (strlen Str$))) 0 (+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#))) (Alpha2Number (substr Str$ 2)) ) ) ) (setq xlApp (vlax-get-or-create-object "Excel.Application")) (setq xlBook (vlax-get-property xlApp 'Workbooks)) (setq xlSheet (vlax-get-property xlApp 'Sheets)) (setq AcSheet (vlax-get-property xlApp 'ActiveSheet)) (setq Cells (vlax-get-property AcSheet 'Cells)) (setq xlcols (vlax-get-property AcSheet 'Columns)) (setq xlrows (vlax-get-property AcSheet 'Rows)) (setq MaxCols (vlax-get-property xlcols 'Count)) (setq MaxRows (vlax-get-property xlrows 'Count)) (cond ((= rc "R") (setq ColumnInput (getstring "\n Enter the Column Number to find the last Row (A,B,C.. or 1,2,3..) : ")) (setq ColumnInt (atoi ColumnInput)) (if (and (= ColumnInt 0) (/= CoumnInput "0")) (progn (setq ColumnInput (strcase ColumnInput)) ) (progn (setq ColumnInput (Number2Alpha ColumnInt)) ) ) (setq addr (strcat ColumnInput (itoa MaxRows))) (setq finddirection 3) ) ((= rc "C") (setq RowInput (getint "\n Enter the Row Number to find the last Column (1,2,3..) : ")) (setq addr (strcat (Number2Alpha MaxCols) (itoa RowInput))) (setq finddirection 1) ) ) (setq Rng (vlax-get-property AcSheet 'Range addr)) (setq whatyoufind (vlax-get-property Rng 'End finddirection)) ; finddirection : 1 = xlToLeft, 2 = xlToRight, 3 = xlUp, 4 = xlDown (setq whatyoufindrow (vlax-get-property whatyoufind 'row)) (setq whatyoufindcol (vlax-get-property whatyoufind 'column)) (setq whatyoufindtxt (vlax-variant-value (vlax-get-property whatyoufind 'value))) (if (= whatyoufindtxt nil) (setq whatyoufindtxt "")) (vlax-invoke whatyoufind 'select) ;If you don't need to select a cell, delete this line (cond ((= rc "R") (princ "Last non-empty cell in column [ ") (princ ColumnInput) (princ " (= ") (princ (Alpha2Number ColumnInput)) (princ " ) ] is in row [ ") (princ whatyoufindrow) ) ((= rc "C") (princ "Last non-empty cell in Row [ ") (princ RowInput) (princ " ] is in column [ ") (princ (Number2Alpha whatyoufindcol)) (princ " (= ") (princ whatyoufindcol) (princ " )") ) ) (princ " ], with value : [ ") (princ whatyoufindtxt) (princ " ].") (vlax-release-object xlApp) (princ) ) command = FLRE then input column number by number or alphabet Edited September 8, 2023 by exceed add find last column function 1 Quote Link to comment Share on other sites More sharing options...
exceed Posted September 8, 2023 Share Posted September 8, 2023 (edited) 23 hours ago, ekko said: @hosneyalaa @Stefan BMR The very good ideas provided by the two, I tried it, and it perfectly fulfilled my needs. But I still want to find out the cause of the error. About (setq lastCell (vlax-invoke ColumnD 'Find "*" nil xlValues xlByColumns xlNext) ) Why can’t this code achieve this function? Is my method of use wrong? Who can help me solve this problem? (defun c:test (/ xlApp xlBook xlSheet atsheet columnD Columnall row col cellValue ) (vl-load-com) (setq xlApp (vlax-get-or-create-object "Excel.Application")) (setq xlBook (vlax-get-property xlApp 'Sheets) xlSheet (vlax-get-property xlBook 'Item "Sheet1") atsheet (vlax-invoke-method xlSheet 'Activate) ) (setq ColumnD (vlax-get-property xlSheet 'Range "D:D")) (setq lastCell (vlax-invoke ColumnD 'Find "*" nil -4163 1 2 2) ; 'Fine what after lookin lookat SearchOrder SearchDirection ) ; xlfindlookin > xlComments = -4144, xlCommentsThreaded = -4184, xlFormulas = -4123, xlValues = -4163 ; xlLookAt > xlWhole = 1, xlPart = 2 ; XlSearchOrder > xlByRows = 1, xlByColumns = 2 ; XlSearchDirection > xlNext = 1, xlPrevious = 2 (if lastCell (progn (setq row (vlax-get lastCell 'Row)) (setq col (vlax-get lastCell 'Column)) (setq cellValue (vlax-get lastCell 'Value)) (if (numberp cellValue) (setq cellValue (rtos cellValue 2 2)) ) (princ (strcat "Last non-empty cell in column D is in row " (itoa row) ", column " (itoa col) ", with value: " "\"" cellValue "\"" ) ) ) (princ "No non-empty cell found in column D") ) (vlax-release-object columnD) (vlax-release-object lastCell) (vlax-release-object xlApp) (gc) (gc) (princ) ) 1. If you want to use this, you must change all values starting with xl~ to constants. Because that is a value known to excel only. not a value known to autolisp. Just think of it like this. (setq xlValue -4163) (setq xlWhole 1) ..... etc excel vba has this values in those variables already you can find it in microsoft pages. like this link https://learn.microsoft.com/en-us/office/vba/api/excel.xlfindlookin 2. there are two ways to find the last cell. forward way as you mentioned, if the middle of the list is empty, trapping this requires knowing the total row count, If there are cells with values in the remaining cells performing it again, loop this It's not reasonable. and need to find empty cell "" instead of "*". but you cannot enter the value nil or "" as the "what" argument of the excel find function. can't put (strcat (chr 34) (chr 34)) or "\"\"" or :vlax-false also. by vba can find "" but by autolisp cannot. as far as i know Therefore, if you implement this, you have to search for the cell value with "*" and collect more data, so it is inefficient. reverse way changes the search direction. it's the universal way to find the last cell. (setq lastCell (vlax-invoke ColumnD 'Find "*" nil -4163 1 2 2) ; 'Find what after lookin lookat SearchOrder SearchDirection Edited September 8, 2023 by exceed 1 1 Quote Link to comment Share on other sites More sharing options...
ekko Posted September 8, 2023 Author Share Posted September 8, 2023 8 hours ago, exceed said: (defun c:test (/ xlApp xlBook xlSheet atsheet columnD Columnall row col cellValue ) (vl-load-com) (setq xlApp (vlax-get-or-create-object "Excel.Application")) (setq xlBook (vlax-get-property xlApp 'Sheets) xlSheet (vlax-get-property xlBook 'Item "Sheet1") atsheet (vlax-invoke-method xlSheet 'Activate) ) (setq ColumnD (vlax-get-property xlSheet 'Range "D:D")) (setq lastCell (vlax-invoke ColumnD 'Find "*" nil -4163 1 2 2) ; 'Fine what after lookin lookat SearchOrder SearchDirection ) ; xlfindlookin > xlComments = -4144, xlCommentsThreaded = -4184, xlFormulas = -4123, xlValues = -4163 ; xlLookAt > xlWhole = 1, xlPart = 2 ; XlSearchOrder > xlByRows = 1, xlByColumns = 2 ; XlSearchDirection > xlNext = 1, xlPrevious = 2 (if lastCell (progn (setq row (vlax-get lastCell 'Row)) (setq col (vlax-get lastCell 'Column)) (setq cellValue (vlax-get lastCell 'Value)) (if (numberp cellValue) (setq cellValue (rtos cellValue 2 2)) ) (princ (strcat "Last non-empty cell in column D is in row " (itoa row) ", column " (itoa col) ", with value: " "\"" cellValue "\"" ) ) ) (princ "No non-empty cell found in column D") ) (vlax-release-object columnD) (vlax-release-object lastCell) (vlax-release-object xlApp) (gc) (gc) (princ) ) 1. If you want to use this, you must change all values starting with xl~ to constants. Because that is a value known to excel only. not a value known to autolisp. Just think of it like this. (setq xlValue -4163) (setq xlWhole 1) ..... etc 2. there are two ways to find the last cell. forward way as you mentioned, if the middle of the list is empty, trapping this requires knowing the total row count, If there are cells with values in the remaining cells performing it again, loop this It's not reasonable. and need to find empty cell "" instead of "*". but you cannot enter the value nil or "" as the "what" argument of the excel find function. can't put (strcat (chr 34) (chr 34)) or "\"\"" or :vlax-false also. Therefore, if you implement this, you have to search for the cell value with "*" and collect more data, so it is inefficient. reverse way changes the search direction. it's the universal way to find the last cell. (setq lastCell (vlax-invoke ColumnD 'Find "*" nil -4163 1 2 2) ; 'Fine what after lookin lookat SearchOrder SearchDirection ) Thanks for your help, admire your ability to solve problems, thanks to your knowledge base 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.