Message 1 of 22
Not applicable
01-14-2015
08:28 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi all,
This topic started on this link
Short explanation: I m trying to compare a variable with columnvalue's, get from the right match a row, and then use that rownumber to get all the next columns values.
If you look at the link above Henrique put a lisp together for the first columninteger, Now i m trying to get the next columns, but i keep failing getting a right result. I know that
(1+ (1+ (vlax-get-property Cell 'Column)))
works for the next column but i can't seem to get it working.
I tried to made a subfunction val2 to extract from Column C. The Excel table from the link above isn't changed.
;;; -----------------------------
;;; -- --
;;; -- Made by Subje --
;;; -- Dieter Bevernage --
;;; -- Abicon NV --
;;; -- Belgium --
;;; -- --
;;; -- Many Thanks to ->-> --
;;; -- hmsilva - Henrique --
;;; -- <-<-Made it possible --
;;; -- --
;;; -- Shared wisdom of --
;;; -- Afralisp --
;;; -- Terry Miller --
;;; -- Lee Mac --
;;; -- Hallex --
;;; -- JefferyPSanders --
;;; -- --
;;; -----------------------------
;;; Program Name: FindCells 1.01
;;; First creation on 13/01/2015
;;; Function: Know the value on Column B and find next the values on the next columns related to previeus value
;;; $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
;;; $$ $$
;;; $$ Revision History $$
;;; $$ Rev By Date Description $$
;;; $$ $$
;;; $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
;;; $$ $$
;;; $$ 1 DB 15-01-13 Initial version $$
;;; $$ $$
;;; $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
;;; $$ $$
;;; $$ Overvieuw functions: $$
;;; $$ $$
;;; $$ rtosr $$
;;; $$ ---Used to change a real number into a short real number string $$
;;; $$ $$
;;; $$ Exceldossier $$
;;; $$ ---Main function $$
;;; $$ $$
;;; $$ Sel-str $$
;;; $$ ---Change type excelvalue $$
;;; $$ $$
;;; $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
(defun C:Doss(/ sel-str Exceldossier Val2)
(vl-load-com)
(defun sel-str (ExcelValue / rtosr)
;;http://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/getexcel-lsp-changing-cell-color-of-c...
(defun rtosr (RealNum~ / DimZin# ShortReal$)
(setq DimZin# (getvar "DIMZIN")) (setvar "DIMZIN" 8)
(setq ShortReal$ (rtos RealNum~ 2 8)) (setvar "DIMZIN" DimZin#)
ShortReal$ );defun rtosr
(setq ExcelValue
(cond
((= (type ExcelValue) 'INT) (itoa ExcelValue))
((= (type ExcelValue) 'REAL) (rtosr ExcelValue))
((= (type ExcelValue) 'STR) (vl-string-trim " " ExcelValue))
((/= (type ExcelValue) 'STR) "")
);cond
);setq
)
;;;---Function to find value next to cel with same value as pronounced
(defun Exceldossier (ExcelValue / xlfilename sheetName Excelapp xlSheet
OpenExFile Excelfile MaxRange ExcData Datarange Projectname Column Cell)
;;;---Sheetname
(setq sheetName "Dossiergegevens")
;;;---filelocation
(setq xlfilename "R:\\Autodesk Standards\\test excel\\dossier.xlsx")
;;;---function to start excel
(setq Excelapp (vlax-get-or-create-object "Excel.Application"))
;;;---if no Excel.exe exist errorprompt
(if (not Excelapp)
(progn
(alert "Can't start Excel")
(exit)
)
)
;;;---Put excel invisible
(vla-put-visible Excelapp :vlax-false)
;;;---ignore alerts excel
(vlax-put-property Excelapp 'DisplayAlerts :vlax-False)
;;;---Open file with excel
(setq Excelfile (vl-catch-all-apply 'vla-open
(list (vlax-get-property Excelapp "WorkBooks") xlfileName)))
;;;---look for sheets
(setq xlSheet (vl-catch-all-apply 'vlax-get-property
(list (vlax-get-property Excelfile "Sheets") "Item" sheetName)))
;;;---Make the selected worksheet active
(vlax-invoke-method xlSheet "Activate")
;;;--- cellrange in sheet
(setq MaxRange (vlax-get-property (vlax-get-property xlSheet 'Cells) "range" "B2:B20"))
;; to get the ExcData as a list of strings
(vlax-for x MaxRange
(setq ExcData (cons (sel-str (vlax-variant-value (vlax-get-property x 'Value))) ExcData)))
(setq ExcData (reverse ExcData))
;; to get the next column cell Column projectname
(vlax-for x MaxRange
(if (= (sel-str (vlax-variant-value (vlax-get-property x 'Value))) ExcelValue)
(progn
(setq Cell x)
(setq Projectname (sel-str (vlax-variant-value
(vlax-get-property
(vlax-variant-value
(vlax-get-property
(vlax-get-property xlSheet 'Cells)
'Item
(vlax-get-property Cell 'Row)
(1+ (vlax-get-property Cell 'Column))
)
)
'Value
)
)
)
);setq
cell
);progn
);if
);vlax for
;; If cellvalue has no value or existing go to the next errorphrase
(if Projectname
Projectname
(princ "projectname not found!")
)
)
(defun Val2 (Cell / Definition)
(setq Definition (sel-str (vlax-variant-value
(vlax-get-property
(vlax-variant-value
(vlax-get-property
(vlax-get-property xlSheet 'Cells)
'Item
(vlax-get-property Cell 'Row)
(1+ (1+ (vlax-get-property Cell 'Column)))
)
)
'Value
)
)
)
)
)
;;; aks for projectnumber; Get string?
(while (/= "" (setq ExcelValue
(getstring "\nWhat's the projectnumber? [ Examples: 5020 or VD655 ]:")))
(setq Projectname (Exceldossier ExcelValue))
(Setq Definition (Val2 Cell))
(princ (strcat "\n The value of address " ExcelValue " is: "))
(princ Projectname )
(princ Definition )
)
(princ)
)
Solved! Go to Solution.
