Visual LISP, AutoLISP and General Customization
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Why is Variant value of excel cell returning 1.25359e+006 ?

7 REPLIES 7
SOLVED
Reply
Message 1 of 8
BillZndl
1511 Views, 7 Replies

Why is Variant value of excel cell returning 1.25359e+006 ?

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

 

 

 

 

7 REPLIES 7
Message 2 of 8
Lee_Mac
in reply to: BillZndl

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

 

Message 3 of 8
BillZndl
in reply to: Lee_Mac

(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

 

 

 

 

Message 4 of 8
Lee_Mac
in reply to: BillZndl


@BillZndl wrote:

But how do I get the number to return the same as what is typed in the cell?


I would suggest using the ActiveX Text property of the Range object instead of the Value / Value2 property.

Message 5 of 8
BillZndl
in reply to: Lee_Mac

Thanks,

but you can't get the text property with the UsedRange..

It shows RO and returns nil.

 

 

Message 6 of 8
Lee_Mac
in reply to: BillZndl


@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.

Message 7 of 8
BillZndl
in reply to: Lee_Mac

Okay, thanks!

Should do the job.

Message 8 of 8
Lee_Mac
in reply to: BillZndl

You're welcome Bill.

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report

”Boost