Author Topic: How to change the format of a column or cell in Excel  (Read 386 times)

0 Members and 1 Guest are viewing this topic.

Coder

  • Swamp Rat
  • Posts: 729
How to change the format of a column or cell in Excel
« 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.

Lee Mac

  • Seagull
  • Posts: 12225
  • London, England
Re: How to change the format of a column or cell in Excel
« Reply #1 on: May 13, 2019, 08:23:26 AM »
You need to change the NumberFormat property of the Range object representing the target Column/Cell to a value of "# ?/?" or "# ??/??" depending on the precision required.

Coder

  • Swamp Rat
  • Posts: 729
Re: How to change the format of a column or cell in Excel
« Reply #2 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.

Lee Mac

  • Seagull
  • Posts: 12225
  • London, England
Re: How to change the format of a column or cell in Excel
« Reply #3 on: May 13, 2019, 12:27:14 PM »
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, not the Text property) 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.


Coder

  • Swamp Rat
  • Posts: 729
Re: How to change the format of a column or cell in Excel
« Reply #4 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. 

Coder

  • Swamp Rat
  • Posts: 729
Re: How to change the format of a column or cell in Excel
« Reply #5 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.

Greg B

  • Seagull
  • Posts: 12313
  • Tell me a Joke!
Re: How to change the format of a column or cell in Excel
« Reply #6 on: May 14, 2019, 09:00:29 AM »
Heh....Lee is speaking VBA while Coder is speaking LISP.

MP

  • Seagull
  • Posts: 17342
Re: How to change the format of a column or cell in Excel
« Reply #7 on: May 14, 2019, 09:47:14 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.
\|// Set goal. Experiment tirelessly until
|Oo| practice has become expertise.  Loop.
|- | LinkedIn | Dropbox

Grrr1337

  • Swamp Rat
  • Posts: 689
Re: How to change the format of a column or cell in Excel
« Reply #8 on: May 14, 2019, 10:42:04 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*
(apply ''((a b c)(a b c))
  '(
    (( f L ) (apply 'strcat (f L)))
    (( L ) (if L (cons (chr (car L)) (f (cdr L)))))
    (72 101 108 108 111 32 87 111 114 108 100)
  )
)

MP

  • Seagull
  • Posts: 17342
Re: How to change the format of a column or cell in Excel
« Reply #9 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.

\|// Set goal. Experiment tirelessly until
|Oo| practice has become expertise.  Loop.
|- | LinkedIn | Dropbox

Lee Mac

  • Seagull
  • Posts: 12225
  • London, England
Re: How to change the format of a column or cell in Excel
« Reply #10 on: May 14, 2019, 06:38:45 PM »
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
  17. 0
  18. 0
  19. 0
  20. 0
  21. 0

This yields the following result:


Coder

  • Swamp Rat
  • Posts: 729
Re: How to change the format of a column or cell in Excel
« Reply #11 on: May 15, 2019, 04:37:33 AM »
Very nice Lee.
Thank you so much.

Greg B

  • Seagull
  • Posts: 12313
  • Tell me a Joke!
Re: How to change the format of a column or cell in Excel
« Reply #12 on: May 15, 2019, 08:50:06 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!