All the other cells are returning number values, it's just the first one that giving me fits.
All cells are General format in excel.
(nth 3 excdata)
(1.25359e+006 0.125 10.0 0.75 0.75 12.0 0.625 0.75 11.1017 0.642941 10.4587
9.743 12.9767 0.517941 12.3337 11.743 nil)
I've tried formatting the cell to text , still nothing.
here's what the excel row looks like:
1253585 0.125 10 0.75 0.75 12 0.625 0.75 11.10169 0.6429405 10.458745 9.743 12.97669 0.5179405 12.3337452 11.743
Solved! Go to Solution.
Solved by Lee_Mac. Go to Solution.
Firstly, note that 1253585 is the same as 1.25359e+006 (= 1.25359 x 10^6) to 6 significant figures of precision.
I would imagine that the value you are witnessing is being truncated as a result of the automatic cell formatting in Excel, which interprets the number as numerical content and stores it in scientific notation.
To confirm, what result do you get if you do the following:
(mapcar '(lambda ( x ) (rtos x 2 15)) (nth 3 excdata))
(mapcar '(lambda ( x ) (rtos x 2 15)) test)
("1253585.000000000" "0.125000000000000" "10.00000000000000"
"0.750000000000000" "0.750000000000000" "12.00000000000000" "0.625000000000000"
"0.750000000000000" "11.10168568595789" "0.642940503983112" "10.45874518197478"
"9.743000000000000" "12.97668568595789" "0.517940503983112" "12.33374518197478"
"11.74300000000000")
okay thanks.
But how do I get the nunmer to return the same as what is typed in the cell?
I guess what I'm saying is:
Why is the first cell the only one that returns the number in this way?
here's what the excel row looks like:
1253585 0.125 10 0.75 0.75 12 0.625 0.75 11.10169 0.6429405 10.458745 9.743 12.97669 0.5179405 12.3337452 11.743
@BillZndl wrote:Thanks,
but you can't get the text property with the UsedRange..
It shows RO and returns nil.
As far as I know, the Text property can only be used with a range of a single cell, as shown in the example on MSDN. Hence, you would need to iterate over the cells held by your Range object, and retrieve the Text property for each Cell.
Here is a quick example:
;; Example demonstrating how to read the plain text content of all cells within ;; the UsedRange of the active worksheet of a supplied Excel file. ;; - Lee Mac - 2013-10-29 (defun readexcel ( xls / rtn xlapp xlcls xlcol xlrng xlrow xlsht xlwbk xlwbs ) (if (and (setq xls (findfile xls)) (setq xlapp (vlax-get-or-create-object "excel.application")) ) (progn (setq rtn (vl-catch-all-apply '(lambda ( / col lst row ) (setq xlwbs (vlax-get-property xlapp 'workbooks) xlwbk (vlax-invoke-method xlwbs 'open xls) xlsht (vlax-get-property xlapp 'activesheet) xlrng (vlax-get-property xlsht 'usedrange) xlcls (vlax-get-property xlrng 'cells) xlrow (vlax-get-property xlrng 'rows) xlcol (vlax-get-property xlrng 'columns) ) (repeat (setq row (vlax-get-property xlrow 'count)) (repeat (setq col (vlax-get-property xlcol 'count)) (setq lst (cons (vlax-variant-value (vlax-get-property (vlax-variant-value (vlax-get-property xlcls 'item row col) ) 'text ) ) lst ) ) (setq col (1- col)) ) (setq row (1- row)) ) lst ) ) ) (if (= 'vla-object (type xlwbk)) (vl-catch-all-apply 'vlax-invoke-method (list xlwbk 'close :vlax-false)) ) (vl-catch-all-apply 'vlax-invoke-method (list xlapp 'quit)) (foreach obj (list xlcol xlrow xlcls xlrng xlsht xlwbk xlwbs xlapp) (if (= 'vla-object (type obj)) (vlax-release-object obj) ) ) (gc) (if (vl-catch-all-error-p rtn) (prompt (strcat "\nError: " (vl-catch-all-error-message rtn))) rtn ) ) (prompt "\nUnable to interface with Excel Application.") ) ) (vl-load-com) (princ)
Call the above with the Excel filename:
(readexcel "C:\\YourFile.xls")
Note that the above code snippet is only a draft example to demonstrate the required method described in this thread, and the code should not be used directly in an application.