Find cellname in Excel

[jcartigny]

I would like to get the cell with a specific name in Excel.

In Excel you can give a cell a custom name like "Project_Name".

How do I get the sheet and cell(row,column) of the cell with this custom name ?

Thanks

[lgabriel]

Attached Autolisp program written by Terry Miller, that I found several years ago, which creates a library of fucntions for reading and writing to Excel spreadsheets, using AutoLisp.

I load GetExcel.lsp as part of the start up suite, so when I call any of the functions in the library in another AutoLisp program, it will work without having to add the library functions, in every program.

[Hallex]

Some code from I twicked few years ago, try it

(see my comments within)

(defun C:XLFIND	(/ accell cnt filename icol irow n projname x xlapp xlbook xlcell xlfrange xlrange xlsheet xlsheets)
  (vl-load-com)
  (defun *error* (msg)
    (if
      (and msg
	   (not
	     (member
	       msg
	       '("console break"
		 "Function cancelled"
		 "quit / exit abort"
		 ""
		)
	     )
	   )
      )
       (princ (strcat "\nError: " msg))
    )

    (vl-catch-all-apply
      (function	(lambda	()
		  (vlax-invoke-method xlBook 'Close :vlax-false)
		)
      )
    )
    (vl-catch-all-apply
      (function	(lambda	()
		  (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)
    )
    (princ)
  ) ;_eof *error*

;;============================= main part =======================;;
;; set value to lookup here:
  (setq	projname "Project Name");; <-- change a value ! 
;; change file name here, or use Browser to get the file name : 
  (setq fileName (strcat (getvar "dwgprefix") "FOO.xlsx"));; <-- change a file name ! 
;; 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 xlSheets (vlax-get-property xlBook 'WorkSheets))
	   (setq cnt (vlax-get-property xlSheets 'Count))
	   (setq n 0)
	   (repeat cnt
	     (setq xlSheet (vlax-get-property
			     (vlax-get-property xlBook 'WorkSheets)
			     'Item
			     (setq n (1+ n))
			     )
		   )
	     (vlax-invoke-method xlSheet 'Activate)
	     (setq xlRange (vlax-get-property xlsheet 'UsedRange))
	     (vlax-invoke-method xlRange 'Select)

	     (setq xlFRange (vlax-get-property xlsheet 'UsedRange))
	     (if
	       (and
		 (not
		   (vl-catch-all-error-p
		     (vl-catch-all-apply
		       (function (lambda ()
				   (setq xlCell	(vlax-invoke-method
						  xlRange
						  'Find
						  (vlax-make-variant projname)
						  xlFRange
						  -4163
						  1
						  1
						  1
						  nil
						  nil
						  )
					 )
				   )
				 )
		       )
		     )
		   )
		 xlCell
		 )
		(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 "
				 projname
				 " found at\n"
				 "row: "
				 (itoa iRow)
				 "; column: "
				 (itoa iCol)
				 "\n"
				 "Sheet name: "
				 (vlax-get-property
				   (vlax-get-property
				     (vlax-get-property xlBook 'WorkSheets)
				     'Item
				     n
				     )
				   'Name)
				 )
			 )
		  )
		(alert (strcat projname " not found on this sheet..."))
		)
	     )
	   )
	 )
      )
    (alert (strcat "Problem with Excel running"))
    )
  (*error* nil)
  (gc)
  (princ)
)
(princ "\n\t\t  * Type XLFIND to execute... *")
(princ)

~'J'~

[jcartigny]

Thanks for your reply.

I tried your XLFIND.LSP with the attached file Test.xls.

Cell B8 at sheet Alu 2 is called 'A70_aantal'.

Xlfind could not find a cell with name 'A70_aantal'.

The software found the file and its three sheets.

It gave three times the alert: "A70_aantal not found on this sheet... ".

What is going wrong ?

Most of your code I do understand, except this part:

(setq xlCell (vlax-invoke-method
xlRange
'Find
(vlax-make-variant projname)
xlFRange
-4163
1
1
1
nil
nil
)
)

I hope that my attachment can make my question clear.

Thanks for your reaction.

JC

[Hallex]

Sorry, it was copy-paste problem

Change an address to search for your word:

(setq xlFRange (vlax-get-property xlsheet 'Range (vlax-make-variant "A1")))

Let me know if this will found what you need,

~'J'~

[jcartigny]

Yours suggestion did not quit work as espected, because it missed vl-catch-all-apply, for sheets that did not have the projname.

With your help I could make a short testfunction that worked perfect:

(defun c:xlf2 ()
;; Start Excel session
(setq xlapp (vlax-get-or-create-object "Excel.Application"))
(if (not xlapp)

(progn
(alert "Can't start Excel")

(exit)

)
)
(setq xlBooks (vlax-get xlapp "workbooks"))
(vlax-put-property xlapp "Visible" :vlax-true)
;; Open file
(setq xlBook (vlax-invoke xlBooks 'open "C:\\CTA\\CWall\\Source_310\\Test.xls"))
(setq xlSheets (vlax-get-property xlBook 'WorkSheets))
(setq count (vlax-get-property xlSheets 'Count))
(setq idx 0)
;; Iterate through sheets
(repeat count
(setq idx (1+ idx))
(setq xlSheet (vlax-get-property xlsheets 'Item idx))
;; Find cell
(setq xlFRange (vl-catch-all-apply 'vlax-get-property (list xlsheet 'Range (vlax-make-variant "A100_aantal"))))
(if (vl-catch-all-error-p xlFRange)
(print (strcat "Not found in sheet " (itoa idx)))
(print (strcat "Found in sheet " (itoa idx)))
)
)
(princ)
)

If xlFrange is found, I can get the properties Column, Row, etc.

My problem is solved, thanks a lot.

JC

[Hallex]

You're welcome,

glad you got it to work,

~'J'~