Formatting cells in Excel

[barrikid]

I am currently trying to format some cells in Excel.

I found _ExcelNumberFormat(), but that seems to only be able to convert the cells to a number based format.

I want the cells to be formatted to Text.

#include <excel.au3>
#include <array.au3>

$oExcel = _ExcelBookNew()
_ExcelNumberFormat($oExcel, "@", 1, 1, 500)
If @error Then
    MsgBox(0, "err")
EndIf

$MyClip = ClipGet()

Send("^v")

Why? Copy and paste "4/4" into excel (without quotes). You will get 4-Apr instead of 4/4. If it is in the Text format, it will paste as 4/4

Is there a way to get it to format to Text?

Thanks,

BK

Edited October 6, 2010 by barrikid

[exodius]

Welcome to the forums!

When doing anything in Excel, recording macros is your best friend because it will essentially give you the code - you then just have to convert it to AutoIt's style of COM.

Here's an example of how to do what you want to do:

#include <Excel.au3>

$oExcel = _ExcelBookNew()
$oExcel.Range("A1").Select
$oExcel.Selection.NumberFormat = "@" ; To set a Text format
;~ $oExcel.Selection.NumberFormat = "0.00" ; To set a Number format

_ExcelWriteCell($oExcel, "4/4", "A1")

**Note that you need to set the cell format before you write to it, otherwise you end up with something different.

Edited October 6, 2010 by exodius

[barrikid]

Ah, thanks. I didn't realize that I got a reply till I solved it myself, heh.

Ill check out the macro recording though,

Thanks,

BK

[kawumm3000]

On 10/6/2010 at 7:31 AM, exodius said:

 

**Note that you need to set the cell format before you write to it, otherwise you end up with something different.

Is it possible to change the format after the cell is filled? I get a Excel file and need to change the format in some cells from text to number. How can I do that?

I tried

$oRange.NumberFormat = "0,00"

but it doesn't change anything. 

[water]

Please ahve a look at the wiki: https://www.autoitscript.com/wiki/Excel_UDF#Format_a_range

[kawumm3000]

48 minutes ago, water said:

Please ahve a look at the wiki: https://www.autoitscript.com/wiki/Excel_UDF#Format_a_range

I tried that already. But it looks like it only works before writing in the cell. I need to format the cell after the write. 

[BrewManNH]

4 minutes ago, kawumm3000 said:

But it looks like

That tells us that you didn't really try it, you just looked at what it does without seeing what it does. Try it, and THEN come back here if it doesn't work.

[kawumm3000]

1 minute ago, BrewManNH said:

That tells us that you didn't really try it, you just looked at what it does without seeing what it does. Try it, and THEN come back here if it doesn't work.

As I said.... 

Quote

I tried that already.

The cell is still text. 

Is this forum not for helping? 

[BrewManNH]

Show us your script with what you've tried that doesn't work.

[kawumm3000]

Local $oRangeA = $oWorkbook.ActiveSheet.Range("A13:A"&$iRows)
Local $oRangeB = $oWorkbook.ActiveSheet.Range("B13:B"&$iRows)

$oRangeA.NumberFormat = "dd.mm.yyyy"
$oRangeB.NumberFormat = "0,00"

For $oRangeA it works. For $oRangeB it doesn't work. I also tried "#,##" 

I can change the color font and size of $oRangeB, but can't turn the text into a number.

[water]

If the comma should be the 1000-separator you should try: #,##0

[kawumm3000]

No, it's the decimal separator .

[water]

#,##0.00

[Nine]

If the content of the cells are not rightly formatted, it won't convert non-numeric string into a numeric value.  To help us help you, please provide a runable script like this one :

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Create a new workbook with only 2 worksheets
$oWorkBook = _Excel_BookNew($oExcel, 2)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

_Excel_RangeWrite($oWorkBook,1, "1,25", "A1")

This script writes a string (rightly formatted) that is automatically convert into a number.  No need to add format...

[kawumm3000]

The thing is, it changes the format of the cell to custom but keeps the string as text. 
I don't want to write in the cell. I get the file already filled from an automated process which I can't change. 
But I need the cell as number because I must calculate with it.  

(I hope my english is understandable)

When I try it manually in Excel, I have the same issue. I mark the cell, switch format to "number"in the menue and the value in the cell is still text. I need to edit the cell (F2 and ESC) and then it's a number. Maybe a Excel bug?

 

[Nine]

Like I said, the content of the cells is badly formatted.  You will need to read the bad cells, change it to right format and rewrite it.  You could upload your original .xls for us to see what it can be done, along with a script of what you are trying to achieve.  Help us to help you !

[kawumm3000]

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $oExcel = _Excel_Open()
Local $sWorkbook = "C:\Temp\test.xlsx"

$oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
$oWorkbook.Sheets("Ist-Aufwand1").Activate

Local $iRows = $oExcel.ActiveSheet.UsedRange.Rows.Count

Local $oRangeA = $oWorkbook.ActiveSheet.Range("A13:A"&$iRows)
Local $oRangeB = $oWorkbook.ActiveSheet.Range("F13:F"&$iRows)

$oRangeA.NumberFormat = "TT.MM.JJJJ"
$oRangeB.NumberFormat = "#,##"

_Excel_BookSave($oWorkbook)
_Excel_Close($oExcel, Default, True)

Script & file.

 

test.xlsx

Edited June 18, 2019 by kawumm3000

[Nine]

Working for me (a bit dirty though): 

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\test.xlsx"

$oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
$oWorkbook.Sheets("Ist-Aufwand1").Activate

Local $iRows = $oExcel.ActiveSheet.UsedRange.Rows.Count

Local $aTxt = _Excel_RangeRead ($oWorkbook, 1, "B13:B" & $iRows)
_Excel_RangeWrite($oWorkbook, 1, $aTxt, "B13:B" & $iRows)

Notice that the numerical column is B not F !  Notice also that I use the sheet index 1...

Edited June 18, 2019 by Nine

[water]

Or use a function to translate the string to a number: =VALUE(B13)
Use _Excel_RangeWrite to write this function to an empty cell and then use this new numeric value for further  calculation. Repeat this for all cells in colum B

[kawumm3000]

15 hours ago, Nine said:

Working for me (a bit dirty though): 

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\test.xlsx"

$oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
$oWorkbook.Sheets("Ist-Aufwand1").Activate

Local $iRows = $oExcel.ActiveSheet.UsedRange.Rows.Count

Local $aTxt = _Excel_RangeRead ($oWorkbook, 1, "B13:B" & $iRows)
_Excel_RangeWrite($oWorkbook, 1, $aTxt, "B13:B" & $iRows)

Notice that the numerical column is B not F !  Notice also that I use the sheet index 1...

Yeah, that works!   Thanks alot.