barrikid 0 Posted October 6, 2010 (edited) 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 Share this post Link to post Share on other sites
exodius 1 Posted October 6, 2010 (edited) 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 Share this post Link to post Share on other sites
barrikid 0 Posted October 6, 2010 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 Share this post Link to post Share on other sites
kawumm3000 1 Posted June 18, 2019 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
water 2,392 Posted June 18, 2019 Please ahve a look at the wiki: https://www.autoitscript.com/wiki/Excel_UDF#Format_a_range My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
kawumm3000 1 Posted June 18, 2019 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. Share this post Link to post Share on other sites
BrewManNH 1,305 Posted June 18, 2019 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 GudeHow 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
kawumm3000 1 Posted June 18, 2019 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
BrewManNH 1,305 Posted June 18, 2019 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 GudeHow 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
kawumm3000 1 Posted June 18, 2019 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
water 2,392 Posted June 18, 2019 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 - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
kawumm3000 1 Posted June 18, 2019 No, it's the decimal separator . Share this post Link to post Share on other sites
water 2,392 Posted June 18, 2019 #,##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 - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
Nine 996 Posted June 18, 2019 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... Not much of a signature but working on it... Spoiler Block all input without UAC Save/Retrieve Images to/from Text Tool to search content in au3 files Date Range Picker Sudoku Game 2020 Overlapped Named Pipe IPC x64 Bitwise Operations Multi-keyboards HotKeySet Fast and simple WCD IPC Multiple Folders Selector GIF Animation (cached) Share this post Link to post Share on other sites
kawumm3000 1 Posted June 18, 2019 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
Nine 996 Posted June 18, 2019 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 ! Not much of a signature but working on it... Spoiler Block all input without UAC Save/Retrieve Images to/from Text Tool to search content in au3 files Date Range Picker Sudoku Game 2020 Overlapped Named Pipe IPC x64 Bitwise Operations Multi-keyboards HotKeySet Fast and simple WCD IPC Multiple Folders Selector GIF Animation (cached) Share this post Link to post Share on other sites
kawumm3000 1 Posted June 18, 2019 (edited) #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 Share this post Link to post Share on other sites
Nine 996 Posted June 18, 2019 (edited) 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 1 kawumm3000 reacted to this Not much of a signature but working on it... Spoiler Block all input without UAC Save/Retrieve Images to/from Text Tool to search content in au3 files Date Range Picker Sudoku Game 2020 Overlapped Named Pipe IPC x64 Bitwise Operations Multi-keyboards HotKeySet Fast and simple WCD IPC Multiple Folders Selector GIF Animation (cached) Share this post Link to post Share on other sites
water 2,392 Posted June 18, 2019 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 - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
kawumm3000 1 Posted June 19, 2019 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. Share this post Link to post Share on other sites