TheSwamp

Code Red => AutoLISP (Vanilla / Visual) => Topic started by: Coder on May 13, 2019, 06:42:20 AM

Title: How to change the format of a column or cell in Excel
Post by: Coder on May 13, 2019, 06:42:20 AM
Hello everyone.

How to change the format of a complete column or cell in Excel to appropriate one that accepts the format like 1/4 , 1/2 , 11/4 , 712 5/8 , 656 11/16 ... etc

Thanks in advance.
Title: Re: How to change the format of a column or cell in Excel
Post by: Lee Mac on May 13, 2019, 08:23:26 AM
You need to change the NumberFormat (https://docs.microsoft.com/en-us/office/vba/api/excel.range.numberformat) property of the Range object representing the target Column/Cell to a value of "# ?/?" or "# ??/??" depending on the precision required.
Title: Re: How to change the format of a column or cell in Excel
Post by: Coder on May 13, 2019, 08:56:16 AM
Thank you.

It works if I know the quantity of numbers into a cell but sometimes I use real number to convert to fractional ( inch value ) like: (rtos 123.456 5 4) and that does not come "123 7/16" correctly in its cell and it comes like 1975/16 which is incorrect.
Title: Re: How to change the format of a column or cell in Excel
Post by: Lee Mac on May 13, 2019, 12:27:14 PM
Quote from: Coder on May 13, 2019, 08:56:16 AM
It works if I know the quantity of numbers into a cell but sometimes I use real number to convert to fractional ( inch value ) like: (rtos 123.456 5 4) and that does not come "123 7/16" correctly in its cell and it comes like 1975/16 which is incorrect.

You can populate the Excel cell value (note, you should populate the cell Value property (https://docs.microsoft.com/en-us/office/vba/api/excel.range.value), not the Text property (https://docs.microsoft.com/en-us/office/vba/api/excel.range.text)) with the unformatted number (e.g. 123.456) or, if you require the value to be rounded to a given precision, use (distof (rtos 123.456 5 4) 5), and then apply the formatting within Excel using the NumberFormat property (https://docs.microsoft.com/en-us/office/vba/api/excel.range.numberformat).

Title: Re: How to change the format of a column or cell in Excel
Post by: Coder on May 13, 2019, 06:09:19 PM
I am having a hard time with this :yes: anyway will post the outcome of my tries as soon as I could. 
Title: Re: How to change the format of a column or cell in Excel
Post by: Coder on May 14, 2019, 06:31:41 AM
Hi Lee.

I have it working sometimes but sometimes the value is incorrect.
Code: [Select]
(setq cell (vlax-variant-value (vlax-get-property AppCells "Item" (vlax-make-variant row) (vlax-make-variant col))))
(vlax-put-property cell "NumberFormat" (vlax-make-variant "##/##"))
(vlax-put cell 'value2 string)

I think if the cell format is Fraction then the value automatically would be set correctly.

Thanks.
Title: Re: How to change the format of a column or cell in Excel
Post by: Greg B on May 14, 2019, 09:00:29 AM
Heh....Lee is speaking VBA while Coder is speaking LISP.
Title: Re: How to change the format of a column or cell in Excel
Post by: MP on May 14, 2019, 09:47:14 AM
Quote from: Greg B on May 14, 2019, 09:00:29 AM
Heh....Lee is speaking VBA while Coder is speaking LISP.

<game buzzer> Incorrect. He's using a VBA reference doc to illuminate properties / methods that can be accessed / invoked via any language that does ActiveX, including (ill named) Visual LISP.
Title: Re: How to change the format of a column or cell in Excel
Post by: Grrr1337 on May 14, 2019, 10:42:04 AM
Quote from: MP on May 14, 2019, 09:47:14 AM
Quote from: Greg B on May 14, 2019, 09:00:29 AM
Heh....Lee is speaking VBA while Coder is speaking LISP.

<game buzzer> Incorrect. He's using a VBA reference doc to illuminate properties / methods that can be accessed / invoked via any language that does ActiveX, including (ill named) Visual LISP.

*cough* COM *cough*
Title: Re: How to change the format of a column or cell in Excel
Post by: MP on May 14, 2019, 11:46:56 AM
Semantically yes but in general LISP parlance ActiveX is often the term used to refer to same.

Title: Re: How to change the format of a column or cell in Excel
Post by: Lee Mac on May 14, 2019, 06:38:45 PM
Quote from: Coder on May 14, 2019, 06:31:41 AM
I have it working sometimes but sometimes the value is incorrect.
Code: [Select]
(setq cell (vlax-variant-value (vlax-get-property AppCells "Item" (vlax-make-variant row) (vlax-make-variant col))))
(vlax-put-property cell "NumberFormat" (vlax-make-variant "##/##"))
(vlax-put cell 'value2 string)

Here's a run-through at the console -
Code - Auto/Visual Lisp: [Select]
  1. _$ (setq app (vlax-create-object "excel.application"))
  2. #<VLA-OBJECT _Application 000000000313a888>
  3. _$ (setq wbs (vlax-get-property app 'workbooks))
  4. #<VLA-OBJECT Workbooks 000000000313a738>
  5. _$ (setq wbk (vlax-invoke-method wbs 'add))
  6. #<VLA-OBJECT _Workbook 000000000313a6c8>
  7. _$ (setq wsh (vlax-get-property wbk 'activesheet))
  8. #<VLA-OBJECT _Worksheet 000000000313a5e8>
  9. _$ (setq rng (vlax-get-property wsh 'range "A1"))
  10. #<VLA-OBJECT Range 000000002f59bf68>
  11. _$ (vlax-put-property rng 'value2 (distof (rtos 123.456 5 4) 5))
  12. nil
  13. _$ (vlax-put-property rng 'numberformat "# ??/??")
  14. nil
  15. _$ (vlax-put-property app 'visible -1)
  16. nil
  18. 0
  20. 0
  22. 0
  24. 0
  26. 0

This yields the following result:

(http://lee-mac.com/swamp/excelfractionoutput.png)
Title: Re: How to change the format of a column or cell in Excel
Post by: Coder on May 15, 2019, 04:37:33 AM
Very nice Lee.
Thank you so much.
Title: Re: How to change the format of a column or cell in Excel
Post by: Greg B on May 15, 2019, 08:50:06 AM
Quote from: Grrr1337 on May 14, 2019, 10:42:04 AM
Quote from: MP on May 14, 2019, 09:47:14 AM
Quote from: Greg B on May 14, 2019, 09:00:29 AM
Heh....Lee is speaking VBA while Coder is speaking LISP.

<game buzzer> Incorrect. He's using a VBA reference doc to illuminate properties / methods that can be accessed / invoked via any language that does ActiveX, including (ill named) Visual LISP.

*cough* COM *cough*

I yield!