Error on getting a Excelvalue

[Anonymous]

i try to extract a value of a excelfile

what i got: 

(defun C:GetCells()
  (vl-load-com)
  (defun GetExcel (cellName / xlfilename Excel Excelfile xlSheet myRange cellValue sheetName)
    (setq sheetName "Dossiergegevens")
    (setq xlfilename "dossier.xlsx")
    (setq Excel (vlax-get-or-create-object "Excel.Application"))
    (vla-put-visible Excel :vlax-false)
    (vlax-put-property Excel 'DisplayAlerts :vlax-False)
    (setq Excelfile (vl-catch-all-apply 'vla-open (list (vlax-get-property Excel "WorkBooks") xlfileName)))
    (setq xlSheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property Excelfile "Sheets") "Item" sheetName)))
    (vlax-invoke-method xlSheet "Activate")
    ;;;--- Make the selected worksheet active

    (setq myRange (vlax-get-property (vlax-get-property xlSheet 'Cells) "Range" cellName))
    (setq cellValue(vlax-variant-value (vlax-get-property myRange 'Value2)))

    (vl-catch-all-apply 'vlax-invoke-method (list Excel 'QUIT))
    (vlax-release-object Excel)

    (if (not (vlax-object-released-p myRange))(progn(vlax-release-object myRange)(setq myRange nil)))
    (if (not (vlax-object-released-p xlSheet))(progn(vlax-release-object xlSheet)(setq xlSheet nil)))
    (if (not (vlax-object-released-p Excelfile))(progn(vlax-release-object Excelfile)(setq Excelfile nil)))
    (if (not (vlax-object-released-p Excel))(progn(vlax-release-object Excel)(setq Excel nil)))
    
    (if(= 'safearray (type cellValue))
      (progn
        (setq tempCellValue(vlax-safearray->list cellValue))
        (setq cellValue(list))
        (if(= (length tempCellValue) 1)
          (progn
            (foreach a tempCellValue
              (if(= (type a) 'LIST)
                (progn
                  (foreach b a
                    (if(= (type b) 'LIST)
                      (setq cellValue(append cellValue (list (vlax-variant-value (car b)))))
                      (setq cellValue(append cellValue (list (vlax-variant-value b))))
                    )
                  )
                )
                (setq cellValue(append cellValue (list (vlax-variant-value a))))
              )
            )
          )
          (progn
            (foreach a tempCellValue
              (setq tmpList(list))
              (foreach b a
                (setq tmp(vlax-variant-value b))
                (setq tmpList(append tmpList (list tmp)))
              )
              (setq cellValue(append cellValue tmpList))
            )
          )
        )
      )
    )
    cellValue
  )
  (while(/= "" (setq cellName (getstring "\nCell address to retrieve? [ Examples: B3 or B3:B15 ]:")))
    (setq cellValue(GetExcel cellName))
    (princ (strcat "\n The value of address " cellName " is: "))(princ cellValue)
    )
  (princ)
)

   but i keep getting a error 

Command: GETCELLS
Cell address to retrieve? [ Examples: B3 or B3:B15 ]:b10
; error: bad argument type: VLA-OBJECT #<%catch-all-apply-error%>

try to change some stuff but nothing helped. any thoughts on this?  

thanks!

[Anonymous]

File is: R:\Autodesk Standards\test excel\dossier.xlsx

Could it be that he doesn't recognised the folder?

Excel previeuw added

i m trying to get actually a lisp where i put as example 5001, in command bar after starting the lisp, and then as reaction the lisp fill those other contents in my DWG custom props

[Anonymous]

This seems to work for how it's written. Now i gonna check how i can bend that lisp to my wanted result. 

i think the fault was how the folder was pronounced. 

(defun C:GetCells()
  (vl-load-com)
  (defun GetExcel (cellName / xlfilename Excel Excelfile xlSheet myRange cellValue sheetName)
    (setq sheetName "Dossiergegevens")
    (setq xlfilename "R:\\Autodesk Standards\\test excel\\dossier.xlsx")
    (setq Excel (vlax-get-or-create-object "Excel.Application"))
    (vla-put-visible Excel :vlax-false)
    (vlax-put-property Excel 'DisplayAlerts :vlax-False)
    (setq Excelfile (vl-catch-all-apply 'vla-open (list (vlax-get-property Excel "WorkBooks") xlfileName)))
    (setq xlSheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property Excelfile "Sheets") "Item" sheetName)))
    (vlax-invoke-method xlSheet "Activate")
    ;;;--- Make the selected worksheet active

    (setq myRange (vlax-get-property (vlax-get-property xlSheet 'Cells) "Range" cellName))
    (setq cellValue(vlax-variant-value (vlax-get-property myRange 'Value2)))

    (vl-catch-all-apply 'vlax-invoke-method (list Excel 'QUIT))
    (vlax-release-object Excel)

    (if (not (vlax-object-released-p myRange))(progn(vlax-release-object myRange)(setq myRange nil)))
    (if (not (vlax-object-released-p xlSheet))(progn(vlax-release-object xlSheet)(setq xlSheet nil)))
    (if (not (vlax-object-released-p Excelfile))(progn(vlax-release-object Excelfile)(setq Excelfile nil)))
    (if (not (vlax-object-released-p Excel))(progn(vlax-release-object Excel)(setq Excel nil)))
    
    (if(= 'safearray (type cellValue))
      (progn
        (setq tempCellValue(vlax-safearray->list cellValue))
        (setq cellValue(list))
        (if(= (length tempCellValue) 1)
          (progn
            (foreach a tempCellValue
              (if(= (type a) 'LIST)
                (progn
                  (foreach b a
                    (if(= (type b) 'LIST)
                      (setq cellValue(append cellValue (list (vlax-variant-value (car b)))))
                      (setq cellValue(append cellValue (list (vlax-variant-value b))))
                    )
                  )
                )
                (setq cellValue(append cellValue (list (vlax-variant-value a))))
              )
            )
          )
          (progn
            (foreach a tempCellValue
              (setq tmpList(list))
              (foreach b a
                (setq tmp(vlax-variant-value b))
                (setq tmpList(append tmpList (list tmp)))
              )
              (setq cellValue(append cellValue tmpList))
            )
          )
        )
      )
    )
    cellValue
  )
  (while(/= "" (setq cellName (getstring "\nCell address to retrieve? [ Examples: B3 or B3:B15 ]:")))
    (setq cellValue(GetExcel cellName))
    (princ (strcat "\n The value of address " cellName " is: "))(princ cellValue)
    )
  (princ)
)

[Anonymous]

i don't to seem to get the row/column out of a excel file with this lisp. 

can anyone look into this. It would be great if they search in Column B. And look for "projectnumber" 

thanks 

(defun C:GetCells()
  (vl-load-com)
  (defun GetExcel (Projectnumber / activesheet xlfilename Excel Excelfile xlSheet xlCell acCell xlFRange sheetName iRow iCol)
    (setq sheetName "Dossiergegevens")
    ;Sheetname 
    (setq xlfilename "R:\\Autodesk Standards\\test excel\\dossier.xlsx")
    ;filelocation
    (setq Excel (vlax-get-or-create-object "Excel.Application"))
    ;function to start excel
    (vla-put-visible Excel :vlax-false)
    ;Put excel invisible
    (vlax-put-property Excel 'DisplayAlerts :vlax-False)
    ;ignore alerts excel
    (setq Excelfile (vl-catch-all-apply 'vla-open (list (vlax-get-property Excel "WorkBooks") xlfileName)))
    ;Open file with excel
    (setq xlSheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property Excelfile "Sheets") "Item" sheetName)))
    ;look for sheets
    (vlax-invoke-method xlSheet "Activate")
    ;Make the selected worksheet active

    (setq xlFRange (vlax-get-property xlSheet 'Range (vlax-make-variant Projectnumber )))

    (progn
    (vlax-invoke xlFRange 'Select)
      
		  (setq acCell (vlax-get-property xlSheet 'ActiveCell))
		  (setq	iRow (vlax-get-property acCell 'Row)
			iCol (vlax-get-property acCell 'Column)
			)
		  (alert (strcat "Given value "
				 Projectnumber
				 " found at\n"
				 "row: "
				 (itoa iRow)
				 "; column: "
				 (itoa iCol)
				 "\n"
				 
				 )
			 )
      )
    


    (vl-catch-all-apply 'vlax-invoke-method (list Excel 'QUIT))
    (vlax-release-object Excel)

    ;(if
    ;  (not (vlax-object-released-p xlCell))
    ;  (progn
;	(vlax-release-object xlCell)
;	(setq xlCell nil)))
    (if
      (not (vlax-object-released-p xlSheet))
      (progn
	(vlax-release-object xlSheet)
	(setq xlSheet nil)))
    (if
      (not (vlax-object-released-p Excelfile))
      (progn
	(vlax-release-object Excelfile)
	(setq Excelfile nil)))
    (if
      (not (vlax-object-released-p Excel))
      (progn
	(vlax-release-object Excel)
	(setq Excel nil)))
      

  )
  (while(/= "" (setq Projectnumber (getstring "\nWhat's the projectnumber? [ Examples: 5020 or VD655 ]:")))
    (setq Cellrange(GetExcel Projectnumber))
    (princ (strcat "\n Cell-ID of the "Projectnumber" is: "))(princ Cellrange)
    )
  (princ)

)

[Anonymous]

(defun c:xlf2 ( /   xlfilename sheetName Excelapp xlSheet OpenExFile Excelfile MaxRange ExcData Datarange)
(vl-load-com)
  
  (setq sheetName "Dossiergegevens")
;;;---Sheetname 

  (setq xlfilename "R:\\Autodesk Standards\\test excel\\dossier.xlsx")
;;;---filelocation

  (setq Excelapp (vlax-get-or-create-object "Excel.Application"))
  (if (not Excelapp)
    (progn
      (alert "Can't start Excel")
      (exit)
      )
    )
;;;---function to start excel

  (vla-put-visible Excelapp :vlax-false)
;;;---Put excel invisible

  (vlax-put-property Excelapp 'DisplayAlerts :vlax-False)
;;;---ignore alerts excel

  (setq Excelfile (vl-catch-all-apply 'vla-open (list (vlax-get-property Excelapp "WorkBooks") xlfileName)))
;;;---Open file with excel

  (setq xlSheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property Excelfile "Sheets") "Item" sheetName)))
;;;---look for sheets

  (vlax-invoke-method xlSheet "Activate")
;;;---Make the selected worksheet active

 
;;;--- cellrange in sheet

  (setq MaxRange (vlax-get-property (vlax-get-property xlSheet 'Cells) "range" "B2:B20"))
  ;(setq ExcData(vlax-safearray->list (vlax-variant-value (vlax-get-property MaxRange 'Value))))

  (setq ExcData (vlax-safearray->list (vlax-variant-value (vlax-get-property MaxRange 'Value ))))

 (foreach n ExcData (progn
		      (princ n)
		      (princ (Strcat "\ntest"))
		      )
   (terpri))
  
)

So that's what i got now. i can read the value's of the cell in that area. The result you find at the bottom of this. What i need to do as next step is to look for a given number out of this list. He needs to evualate that cell and get a row number. Sequal with that row number, as second-last result he needs to find the value of the next columns. 

Another qeustion is: What that variant 5 in my result, can't i just show the value? 

Command: XLF2
(#<variant 8 Dossiernummer >)
test
(#<variant 5 5001>)
test
(#<variant 5 5002>)
test
(#<variant 5 5003>)
test
(#<variant 5 5004>)
test
(#<variant 5 5005>)
test
(#<variant 5 5006>)
test
(#<variant 5 5007>)
test
(#<variant 5 5008>)
test
(#<variant 5 5009>)
test
(#<variant 5 5010>)
test
(#<variant 5 5011>)
test
(#<variant 5 5012>)
test
(#<variant 5 5013>)
test
(#<variant 0 >)
test
(#<variant 0 >)
test
(#<variant 0 >)
test
(#<variant 0 >)
test
(#<variant 0 >)
test

[hmsilva]

Hi Subje,

I don't deal with EXCEL for awhile, but, let's see if I can help you.

Instead of

(setq ExcData (vlax-safearray->list (vlax-variant-value (vlax-get-property MaxRange 'Value ))))

 try

(vlax-for x MaxRange (setq ExcData (cons (vlax-variant-value (vlax-get-property x 'Value)) ExcData)))
(setq ExcData (reverse ExcData))

 and, for the next column value for a given value, perhaps something like this

(setq TestValue 5009.0)
(vlax-for x MaxRange
  (if (= (vlax-variant-value (vlax-get-property x 'Value)) TestValue)
    (setq Val (vlax-variant-value
                (vlax-get-property
                  (vlax-variant-value
                    (vlax-get-property
                      (vlax-get-property xlSheet 'Cells)
                      'Item
                      (vlax-get-property x 'Row)
                      (1+ (vlax-get-property x 'Column))
                    )
                  )
                  'Value
                )
              )
    )
  )
)
(if Val
  (princ Val)
  (princ "\nValue not found!")
)

I hope this helps

Henrique

[Anonymous]

Great Henrique, you nailed it.

One more question: as result i see (aartselaar is the value i changed in that cell as test)

Command: XLF2
aartselaar"aartselaar"

 I m wondering why i get two times the same value. I m gonna try to put more values into the lisp to get more column info. 

And i tried to made the testvalue variable but he doesn't seem to find it. I think it has to do with the type of the value projectnumber.

(defun C:XLF2()
(vl-load-com)
  
(defun Exceldossier (Projectnumber /   xlfilename sheetName Excelapp xlSheet OpenExFile Excelfile MaxRange ExcData Datarange cellValue)

  
  (setq sheetName "Dossiergegevens")
;;;---Sheetname 

  (setq xlfilename "R:\\Autodesk Standards\\test excel\\dossier.xlsx")
;;;---filelocation

  (setq Excelapp (vlax-get-or-create-object "Excel.Application"))
  (if (not Excelapp)
    (progn
      (alert "Can't start Excel")
      (exit)
      )
    )
;;;---function to start excel

  (vla-put-visible Excelapp :vlax-false)
;;;---Put excel invisible

  (vlax-put-property Excelapp 'DisplayAlerts :vlax-False)
;;;---ignore alerts excel

  (setq Excelfile (vl-catch-all-apply 'vla-open
		    (list (vlax-get-property Excelapp "WorkBooks") xlfileName)))
;;;---Open file with excel

  (setq xlSheet (vl-catch-all-apply 'vlax-get-property
		  (list (vlax-get-property Excelfile "Sheets") "Item" sheetName)))
;;;---look for sheets

  (vlax-invoke-method xlSheet "Activate")
;;;---Make the selected worksheet active
  

 
;;;--- cellrange in sheet

  (setq MaxRange (vlax-get-property (vlax-get-property xlSheet 'Cells) "range" "B2:B20"))
  
(vlax-for x MaxRange
  (setq ExcData (cons (vlax-variant-value (vlax-get-property x 'Value)) ExcData)))
  (setq ExcData (reverse ExcData))




(vlax-for x MaxRange
  (if (= (vlax-variant-value (vlax-get-property x 'Value)) Projectnumber)
    (setq cellValue (vlax-variant-value
                (vlax-get-property
                  (vlax-variant-value
                    (vlax-get-property
                      (vlax-get-property xlSheet 'Cells)
                      'Item
                      (vlax-get-property x 'Row)
                      (1+ (vlax-get-property x 'Column))
                    )
                  )
                  'Value
                )
              )
    )
  )
)
  )

(while(/= "" (setq Projectnumber
		    (getstring "\nWhat's the projectnumber? [ Examples: 5020 or VD655 ]:")))
(setq cellValue (Exceldossier Projectnumber))
(princ (strcat "\n Cell-Value of the "Projectnumber" is: "))(princ cellValue)
  )
  
;;;  if value is true, show
  
(if cellValue
  (princ (strcat cellValue))
  (princ "\nValue not found!")
)

  
  
)

Thanks a lot.

[hmsilva]

You're welcome, Subje.

You'll need to add a (princ) at the end, to supress the last evalueted function 'echo'...

(setq Projectnumber (getstring "\nWhat's the projectnumber? [ Examples: 5020 or VD655 ]:"))

you are setting the Projectnumber variable as a string, is the excel value a string or a real number?

Henrique

[Anonymous]

Yes idd , i set it as string. Don't know much of excelproperties so i just typed it into excel, If i look at the properties it's listed on standard.

found this in "getcell lisp"
(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

and i think it has to do with this, right? i need to get the properties of projectnumbers same as vlax for X. i would prefer string, as you see in example VD645 isn't a number. Should i change something in excel?

Thanks
Dieter

[hmsilva]

'found this in "getcell lisp"'

is (rtosr ExcelValue) some function, or a typo?

Henrique

[Anonymous]

It's one of the lower made functions:

edit link:

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

[hmsilva]

Subje,

with 'rtosr' function and the cond to set 'ExcelValue' as a string, you only have to set your 'testvalue' as a string.

EDIT: Try

;; as a sub-function
(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
)

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

;; to get the next column cell
(vlax-for x MaxRange
  (if (= (sel-str (vlax-variant-value (vlax-get-property x 'Value))) Projectnumber)
    (setq cellValue (sel-str (vlax-variant-value
			   (vlax-get-property
			     (vlax-variant-value
			       (vlax-get-property
				 (vlax-get-property xlSheet 'Cells)
				 'Item
				 (vlax-get-property x 'Row)
				 (1+ (vlax-get-property x 'Column))
			       )
			     )
			     'Value
			   )
			   )
		)
	  )
    )
)

Henrique

[Anonymous]

Henrique,

hopely i filled in all correct. See at the end. 

If i put Projectname in into autocad, after command XLF2, i get

Command: XLF2
What's the projectnumber? [ Examples: 5020 or VD655 ]:5008
 The value of address 5008 is: nil
What's the projectnumber? [ Examples: 5020 or VD655 ]:
Value not found!
Command:

-> Value of the adress he shows: it's 5008

-> maybe it has to do with asking a string? string to real. I hope with all i'm saying it's make some sense :p. 

;; as a sub-function
(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
)


(defun C:XLF2()
(vl-load-com)

;;;---Function to find value next to cel with same value as pronounced 
(defun Exceldossier (ExcelValue / xlfilename sheetName Excelapp xlSheet
 OpenExFile Excelfile MaxRange ExcData Datarange cellValue )

;;;---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 (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

 (vlax-for x MaxRange
 (if (= (sel-str (vlax-variant-value (vlax-get-property x 'Value))) ExcelValue)
 (setq cellValue (sel-str (vlax-variant-value
 (vlax-get-property
 (vlax-variant-value
 (vlax-get-property
 (vlax-get-property xlSheet 'Cells)
 'Item
 (vlax-get-property x 'Row)
 (1+ (vlax-get-property x 'Column))
 )
 )
 'Value
 )
 )
 )
 )
 )
 )
)
 
;;; aks for projectnumber; Get string? What if he doesn't find a string?
;;; any other ways to search a cell with a property 5009.0?
 
(while(/= "" (setq ExcelValue
 (getstring "\nWhat's the projectnumber? [ Examples: 5020 or VD655 ]:")))

(setq cellValue (Exceldossier ExcelValue))
(princ (strcat "\n The value of address " ExcelValue " is: "))(princ cellValue)
 )
 
;;; if value is true, show
 
(if cellValue
 (princ cellValue)
 (princ "\nValue not found!")
)

(princ) 
 
)

[hmsilva · Accepted solution]

Untested...

(defun C:XLF2(/ sel-str Exceldossier)
(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 cellValue )

;;;---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 (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

 (vlax-for x MaxRange
 (if (= (sel-str (vlax-variant-value (vlax-get-property x 'Value))) ExcelValue)
 (setq cellValue (sel-str (vlax-variant-value
 (vlax-get-property
 (vlax-variant-value
 (vlax-get-property
 (vlax-get-property xlSheet 'Cells)
 'Item
 (vlax-get-property x 'Row)
 (1+ (vlax-get-property x 'Column))
 )
 )
 'Value
 )
 )
 )
 )
 )
 )
(if cellValue
cellValue
(princ "Value not found!")
)
)
 
;;; aks for projectnumber; Get string? What if he doesn't find a string?
;;; any other ways to search a cell with a property 5009.0?
 
(while (/= "" (setq ExcelValue
 (getstring "\nWhat's the projectnumber? [ Examples: 5020 or VD655 ]:")))

(setq cellValue (Exceldossier ExcelValue))
(princ (strcat "\n The value of address " ExcelValue " is: "))(princ cellValue)
 )
(princ) 
)

 You'll have to release the Objects.

If possible, post a sample dossier.xlsx.

Henrique

[Anonymous]

Yes that was one of my last qeustion how to release it, i tried halfway to put it in, but got a error.

Gonna link the excel right away (brought it to .XLS; xlsx can't upload).

It's just basic, meaning is that we have around 20 column and we can pull out 18 value's with one variable in column B. (and with luck we can link it to my custom dwg props).

If i get this one working i can figure or try alone for the next step till i m stuck(columns and dwgprops) so i still can learn how lisp thinks on this way, but i will keep posting my steps ;). Gonna test it within 20 minutes. 

Thanks Henrique

[Anonymous]

Just tested it, Henrique,

and you got it. Thanks a lot mate. That knowledge is a gift ;).

[hmsilva]

You're welcome, Subje
Glad I could help

Henrique