FIND method in Excel

[Anonymous]

I have a Visual LSIP program to find a specific value in Excel. I'm able to open the excel file and point to a specific range. However, I cannot get the "find" method to work. It always gives me the following error:

"Automation Error - Description not provided"

See the program below.

All variables in the program are assigned a value until it gets to the myRow variable. The program fails at this point.

Does anyone have any ideas how I can get this to work. Much of the program was pasted over from some other examples I've found on the internet.



(defun c:extest ()
(vl-load-com)
(setq sheetName "OUTSIDE WALL"
hgt "93")

;;;--- Get the system drive
(setq sysDrive (getenv "systemdrive"))

;;;--- If the excel object library is not found...load it
(if (null Library)
(progn

;;;--- Find out which version we should use
(setq Library
(cond
((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office\\Excel8.olb")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office\\Excel9.olb")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office\\Excel10.olb")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office\\Excel.exe")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office10\\Excel.exe")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office11\\Excel.exe")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office11\\XL5EN32.OLB")))
)
)

;;;--- If the library was found...
(if Library
(progn

;;;--- Strip off
(setq LibVer (substr (vl-filename-base Library) 6))
(cond
((= LibVer "8")(princ "\n Opening Excel Version 8..."))
((= LibVer "9")(princ "\n Opening Excel Version 9..."))
((= LibVer "1")(princ "\n Opening Excel Version 10..."))
((= LibVer "") (princ "\n Opening Excel Version 2000+..."))
((= LibVer "3")(princ "\n Opening Excel Version 2003..."))

)
(vlax-import-type-library
:tlb-filename Library
:methods-prefix "XLM-"
:properties-prefix "XLP-"
:constants-prefix "XLC-"
)
)
(alert "Excel Object Library was not found!\n\nLook inside the XL_Readme.txt file for HELP.")
)
)
)


;;;--- If an excel application is not loaded, proceed...

(if (null myApp)
(progn

;;;- Select an Excel workbook
(setq fileName "c:/cad/support/tafco/shear sizes/shear sizes.xls")

;;;--- Verify the path to the file
(setq fileName(findfile fileName))

;;;--- Set the default file name globally for next use
(setq oldFileName fileName)

(princ "\n Opening Excel file...")

;;;--- If Excel...
(if(setq myapp(vlax-get-or-create-object "Excel.Application"))
(progn

;;;--- Open the workbook
(vlax-invoke-method (vlax-get-property myapp 'WorkBooks) 'Open fileName)

;;;--- Set it to invisible mode
(vla-put-visible myApp 1)

;;;--- Get the workbooks object
(setq myWBooks(vlax-get myApp "Workbooks"))

;;;--- Open the excel file
(setq myWBook(vla-open myWBooks fileName))

;;;;--- Get the sheets object
(setq mySheets(vlax-get myWBook "Sheets"))

;;;--- Get the selected worksheet
(setq mySht(vlax-get-property mySheets 'Item sheetName))

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

(setq myRange (xlp-get-range mysht "B4:B100"))
(setq aftRange (xlp-get-range mysht "B3"))
;;Search 1st column
;;
;; Use FIND method of Excel with defaults
;;
(setq myRow (xlm-find myRange ;Where to search
hgt ;String to search for
aftRange ;After cell
xlc-xlvalues ;Check values
xlc-xlwhole ;match whole or part
nil
nil
)
)


)
)
)
)


(cond
(
(not(vlax-object-released-p myApp))
(vlax-invoke-method myApp 'QUIT)
(vlax-release-object myApp)
)
)
);end function

[Anonymous]

Not sure about but I think Find function is not available in AutoCAD , maybe I am wrong
I use in this case looping through desired range, i.e.

[code]
(defun c:extest (/ adres filename hgt i iCol Row iRows sheetName
x xlApp xlBook xlCell xlRange xlSheet)
(vl-load-com)
(setq sheetName "OUTSIDE WALL"
hgt "93.0") ; make sure column cells were in desired format
(setq fileName (strcat (getvar "dwgprefix") "shear sizes.xls"))
;;;--- Verify the path to the file
(setq fileName(findfile fileName))
(if (not (setq xlApp (vlax-get-object "Excel.Application")))
(setq xlApp (vlax-create-object "Excel.Application"))
)

(if xlApp
(progn
(vlax-put-property xlApp "Visible" :vlax-true)
(if
(not
(vl-catch-all-error-p
(vl-catch-all-apply
(function (lambda ()
(setq
xlBook (vlax-invoke-method
(vlax-get-property xlApp 'WorkBooks)
"Open"
fileName)
)
)
)
)
)
)
(progn
(vlax-invoke-method xlBook "Activate")
(setq xlSheet (vlax-get-property
(vlax-get-property xlBook "WorkSheets") "Item" sheetName))
(vlax-invoke-method xlSheet "Activate")
(setq xlRange (vlax-get-property xlSheet "Range" "B3:B999"))
(vlax-invoke-method xlRange "Select")
(setq iCol (vlax-get-property xlRange "Column"))
(setq iRows (vlax-get-property (vlax-get-property xlRange "Rows") "Count")
iRow (vlax-get-property xlRange "Row"))
(setq i 1)

(repeat (1- iRows)
(setq i (1+ i)); loop starting from next cell in the column "B"

(setq xlCell (vlax-get-property xlSheet "Range" (strcat "B" (itoa (+ iRow i)))))
(princ (vl-princ-to-string
(vlax-variant-value
(vlax-get-property
xlCell
"Value"))))
(princ "\n")

(if (eq hgt (vl-princ-to-string
(vlax-variant-value
(vlax-get-property
xlCell
"Value"))))



(setq adres (strcat "B" (itoa (+ iRow i))))))

(vlax-invoke-method xlBook "Close" :vlax-false)
(vlax-invoke-method xlApp "Quit")
)
)))
(mapcar (function (lambda (x)
(vl-catch-all-apply
(function (lambda ()
(progn
(vlax-release-object x)
(setq x nil)
)
)
)
)
)
)
(list xlCell xlRange xlSheet xlBook xlApp)
)
(if adres
(alert (strcat "Found in cell: " adres)))

(gc)
)
;---------------code end

Change code to your needs

Fatty

~'J'~
[/code]

[Anonymous]

The "After cell" MUST fall within the search range....you have specified a
value outside the range.
The find method takes 8 arguments, you provide only 6.

This worked for me:

(setq myRow (xlm-find myRange ;Where to search
hgt ;String to search for
nil ;After cell
xlc-xlvalues ;Check values
xlc-xlwhole ;match whole or part
nil
nil
nil
nil
)
)




wrote in message news:5370521@discussion.autodesk.com...
I have a Visual LSIP program to find a specific value in Excel. I'm able to
open the excel file and point to a specific range. However, I cannot get the
"find" method to work. It always gives me the following error:

"Automation Error - Description not provided"

See the program below.

All variables in the program are assigned a value until it gets to the myRow
variable. The program fails at this point.

Does anyone have any ideas how I can get this to work. Much of the program
was pasted over from some other examples I've found on the internet.



(defun c:extest ()
(vl-load-com)
(setq sheetName "OUTSIDE WALL"
hgt "93")

;;;--- Get the system drive
(setq sysDrive (getenv "systemdrive"))

;;;--- If the excel object library is not found...load it
(if (null Library)
(progn

;;;--- Find out which version we should use
(setq Library
(cond
((findfile (strcat sysDrive "\\Program Files\\Microsoft
Office\\Office\\Excel8.olb")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft
Office\\Office\\Excel9.olb")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft
Office\\Office\\Excel10.olb")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft
Office\\Office\\Excel.exe")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft
Office\\Office10\\Excel.exe")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft
Office\\Office11\\Excel.exe")))
((findfile (strcat sysDrive "\\Program Files\\Microsoft
Office\\Office11\\XL5EN32.OLB")))
)
)

;;;--- If the library was found...
(if Library
(progn

;;;--- Strip off
(setq LibVer (substr (vl-filename-base Library) 6))
(cond
((= LibVer "8")(princ "\n Opening Excel Version 8..."))
((= LibVer "9")(princ "\n Opening Excel Version 9..."))
((= LibVer "1")(princ "\n Opening Excel Version 10..."))
((= LibVer "") (princ "\n Opening Excel Version 2000+..."))
((= LibVer "3")(princ "\n Opening Excel Version 2003..."))

)
(vlax-import-type-library
:tlb-filename Library
:methods-prefix "XLM-"
:properties-prefix "XLP-"
:constants-prefix "XLC-"
)
)
(alert "Excel Object Library was not found!\n\nLook inside the
XL_Readme.txt file for HELP.")
)
)
)


;;;--- If an excel application is not loaded, proceed...

(if (null myApp)
(progn

;;;- Select an Excel workbook
(setq fileName "c:/cad/support/tafco/shear sizes/shear sizes.xls")

;;;--- Verify the path to the file
(setq fileName(findfile fileName))

;;;--- Set the default file name globally for next use
(setq oldFileName fileName)

(princ "\n Opening Excel file...")

;;;--- If Excel...
(if(setq myapp(vlax-get-or-create-object "Excel.Application"))
(progn

;;;--- Open the workbook
(vlax-invoke-method (vlax-get-property myapp 'WorkBooks) 'Open
fileName)

;;;--- Set it to invisible mode
(vla-put-visible myApp 1)

;;;--- Get the workbooks object
(setq myWBooks(vlax-get myApp "Workbooks"))

;;;--- Open the excel file
(setq myWBook(vla-open myWBooks fileName))

;;;;--- Get the sheets object
(setq mySheets(vlax-get myWBook "Sheets"))

;;;--- Get the selected worksheet
(setq mySht(vlax-get-property mySheets 'Item sheetName))

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

(setq myRange (xlp-get-range mysht "B4:B100"))
(setq aftRange (xlp-get-range mysht "B3"))
;;Search 1st column
;;
;; Use FIND method of Excel with defaults
;;
(setq myRow (xlm-find myRange ;Where to search
hgt ;String to search for
aftRange ;After cell
xlc-xlvalues ;Check values
xlc-xlwhole ;match whole or part
nil
nil
)
)


)
)
)
)


(cond
(
(not(vlax-object-released-p myApp))
(vlax-invoke-method myApp 'QUIT)
(vlax-release-object myApp)
)
)
);end function

[Anonymous]

I agree with you Jeff
That was my mistake, function 'Find' works for me too
Thanks

(defun c:extest (/ adres filename hgt i iCol Row iRows sheetName
x xlApp xlBook xlCell xlRange xlSheet)
(vl-load-com)
(setq sheetName "OUTSIDE WALL"
hgt "93") ; test certain format in Excel
(setq fileName (strcat (getvar "dwgprefix") "shear sizes.xls"))
;;;--- Verify the path to the file
(setq fileName(findfile fileName))
(if (not (setq xlApp (vlax-get-object "Excel.Application")))
(setq xlApp (vlax-create-object "Excel.Application"))
)

(if xlApp
(progn
(vlax-put-property xlApp "Visible" :vlax-true)
(if
(not
(vl-catch-all-error-p
(vl-catch-all-apply
(function (lambda ()
(setq
xlBook (vlax-invoke-method
(vlax-get-property xlApp 'WorkBooks)
"Open"
fileName)
)
)
)
)
)
)
(progn
(vlax-invoke-method xlBook "Activate")
(setq xlSheet (vlax-get-property
(vlax-get-property xlBook "WorkSheets") "Item" sheetName))
(vlax-invoke-method xlSheet "Activate")
(setq xlRange (vlax-get-property xlSheet "Range" "B3:B999"))
(vlax-invoke-method xlRange "Select")
(setq iCol (vlax-get-property xlRange "Column"))
(setq iRows (vlax-get-property (vlax-get-property xlRange "Rows") "Count")
iRow (vlax-get-property xlRange "Row"))

(setq xlFRange (vlax-get-property xlsheet "Range" "B3"))
(if
(not
(vl-catch-all-error-p
(vl-catch-all-apply
(function (lambda ()
(setq xlCell (vlax-invoke-method xlRange "Find" (vlax-make-variant hgt)
xlFRange
-4163 1 1 1 nil nil)))))))
(progn
(vlax-invoke-method xlCell "Select")

(setq acCell (vlax-get-property xlApp "ActiveCell"))
(setq iRow (vlax-get-property acCell "Row")
iCol (vlax-get-property acCell "Column"))
(alert (strcat "Given value " hgt " found at\n"
"column " (itoa iCol) ", row " (itoa iRow) )))
(alert (strcat "Nothing found" )))


(vlax-invoke-method xlBook "Close" :vlax-false)
(vlax-invoke-method xlApp "Quit")
)
)))
(mapcar (function (lambda (x)
(vl-catch-all-apply
(function (lambda ()
(progn
(vlax-release-object x)
(setq x nil)
)
)
)
)
)
)
(list acCell xlCell xlFrange xlRange xlSheet xlBook xlApp)
)

(gc)
(princ)
)

Fatty

~'J'~