0 Members and 1 Guest are viewing this topic.
Hahasleep with angels
...or this
Quote from: alanjt on May 31, 2011, 11:05:47 AM...or thisHow did you record this ?
it there a way to count how many filled rows there are in an excel file?
(defun readcolumn (xlsheet colnum / c1 cel cels celvalue columndata r1 rows xlrange xlrow) (vlax-invoke-method xlsheet "Activate") ;optional (setq xlrange (vlax-get-property xlsheet "UsedRange")) (setq xlrow (vlax-variant-value (vlax-get-property (vlax-get-property xlrange "Rows") "Item" colnum))) (setq rows (vlax-get-property (vlax-get-property xlrange "Rows") "Count") ) (vlax-invoke-method xlrange "Activate") ;optional (vlax-invoke-method xlrange "Select") ;optional (setq cels (vlax-get-property xlsheet "Cells")) (setq r1 1 ; initial row number in column c1 colnum ; given column number ) (while (<= r1 rows) (setq cel (vlax-variant-value (vlax-get-property cels "Item" ;; row number : (vlax-make-variant r1 vlax-vblong) ;; column number : (vlax-make-variant c1 vlax-vblong)))) (setq celvalue (vlax-variant-value (vlax-get-property cel "Value2"))) (setq columndata (cons (vl-princ-to-string celvalue) columndata)) (setq r1 (1+ r1 ))) (reverse columndata) )
(setq filepath (getfiled "* Open an Excel file to get value of selection : *" (getvar "dwgprefix") "xls*" 12))(setq xlapp (vlax-get-or-create-object "Excel.Application"))(setq xlbooks (vlax-get-property xlapp 'workbooks) xlbook (vl-catch-all-apply 'vla-open (list (vlax-get-property xlapp 'workbooks) filepath)) ; [u]I get an error at xlbook[/u] xlsheets (vlax-get-property xlbook 'sheets) xlsheet (vlax-get-property xlsheets 'item "BW4 Content") ;<-- change sheet name or number here xlcells (vlax-get-property xlsheet 'cells)); begin writing ("celladdress" "value")(foreach item stii-xl-data (setcellbyaddress xlcells (car item) (cadr item)))
i just want to count how many filled cells (or rows) are in column Awithout having to know or open the file and scroll to the end
(setq xlrange (vlax-get-property xlsheet 'UsedRange))
(setq xlrange(vlax-get-property (vlax-get-property (vlax-get-property xlsheet 'UsedRange) 'Columns)'Item 1))))
;;------------------------------code start-------------------------------------;; ((lambda (/ childs numrows path root sname xlapp xlbook xloid xlrange xlsheet) (vl-load-com) (setq root "HKEY_CURRENT_USER\\Software\\Microsoft\\Office") (setq childs (vl-registry-descendents root)) (setq xloid (strcat (strcat "excel.application." (itoa (fix (apply 'max (mapcar 'atof childs)))) ) )) (setq path (getfiled "Select Excel file :" (getvar "dwgprefix") "xls;xlsx" 16 ) ) (setq xlapp (vlax-get-or-create-object xloid)) (vla-put-visible xlapp :vlax-true) (vlax-put-property xlapp 'DisplayAlerts :vlax-false) (setq xlbook (vl-catch-all-apply 'vla-open (list (vlax-get-property xlapp 'WorkBooks) path))) (setq xlsheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property xlbook 'Sheets) 'Item 1)));<-- sheet name or sheet number is allowed (vlax-invoke-method xlsheet 'Activate) (setq sname (vl-catch-all-apply 'vlax-get-property (list xlsheet 'Name))) (setq xlrange (vlax-get-property xlsheet 'UsedRange)) (setq numrows (vlax-get-property (vlax-get-property xlrange 'Rows) 'Count) ) (alert (strcat "Number of used rows on sheet " "\"" sname "\"" " is: " (itoa numrows))) (vlax-invoke-method xlbook 'Close) (vlax-put-property xlapp 'DisplayAlerts :vlax-true) (vlax-invoke-method xlapp 'Quit) (mapcar '(lambda (x) (vl-catch-all-apply '(lambda () (vlax-release-object x) ) ) ) (list xlrange xlsheet xlbook xlapp) ) (setq xlapp nil) (gc) (gc) (gc) (princ)));;------------------------------code end-------------------------------------;;
FixoThere seems to be a hitch...This happened when I was testing a few days ago, but later the function worked fine.I noticed that I had to wait between testing so Excel could release the file.But today the file had not been opened until I tried the XLdemo function.