Jump to content
Sign in to follow this  
barrikid

Formatting cells in Excel

Recommended Posts

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 by barrikid

Share this post


Link to post
Share on other sites

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 by exodius

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
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? 

Share this post


Link to post
Share on other sites

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


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

#,##0.00


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

 

Share this post


Link to post
Share on other sites

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 !

Share this post


Link to post
Share on other sites
#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 by kawumm3000

Share this post


Link to post
Share on other sites

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 by Nine

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
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. :D

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...