Excel cell formatting

4 posts in this topic

Hello again dear forum users!

I just spent 20-30 minutes searching for it in the forums, but so far, I haven't found it. If you happend to have a topic with this question pls link it.

So, I would like to format a cell I inserted in to my excel doc, with excelRangeInsert. I would Like to format it to currency and if possible set decimal places to display to zero.

How do I do that? Didn't find corresponding  _excel UDF, and don't know how to do it with COM.

Thank you for the help

Share this post

Link to post
Share on other sites

from Helpfile:



Writes value(s) or formula(s) to a cell or a cell range on the specified workbook and worksheet


Share this post

Link to post
Share on other sites

I don't get it.

If I write something (a string) in to a cell like: =SUM(range) and then I want to instruct the cell to display the value as a kind of currency, how do I do it?

If I read the value, it gives back the function, like =SUM(range) I cant transformt that to integer and Round() it. and add regex USD at the end.

Share this post

Link to post
Share on other sites

This page in the wiki should give you an idea :)

My UDFs and Tutorials:


Active Directory (2016-08-18 - Version - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
ADO - Wiki


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

  • Similar Content

    • Virgilio1
      By Virgilio1
      Salve Amici,
      in un mio progetto vorrei utilizzare una dll per creare dei file excel senza utilizzare l'applicativo Excel.
      Ora dalla versione 2007 di Office la Microsoft utilizza per i file un nuovo formato aperto "Microsoft Open XML format".
      in PHP questo è molto semplice utilizzando la Libreria PHPExcel (
      Cercando in rete ho trovato una dll che dovrebbe fare lo stesso ( ma non ho assolutamente le capacita di integrare la Dll in autoit, qualcuno mi può aiutare ?
      Sarebbe veramente molto efficiente poter creare e manipolare file excel in autoit senza dover caricare in memoria l'applicativo Excel.
      Hello friends,
      in my project I want to use a dll to create the excel file without using the Excel application.
      Now from the Microsoft Office 2007 version uses for the files a new open format "Microsoft Open XML format".
      PHP This is very simple using the Library PHPExcel (
      Searching the net I found a dll that should do the same ( but I have absolutely the ability to integrate the .dll in autoit, anyone can help me?
      It would really be very efficient to create and manipulate Excel files into memory autoit without having to load the Excel application.
      Thank you
    • Sergy
      By Sergy
      I have price-list in xls. When I open it by _Excel_Open and _Excel_BookOpen and after that close by _Excel_Close, I have a message about "Save changes?"
      I try open it in read-only mode, but no changes made. I still see annoing message.
      Windows 10 prof, MS Office 2007.
      #include <Excel.au3> #include <MsgBoxConstants.au3> ConsoleWrite(@AutoItVersion) Global $sPriceFile = @ScriptDir&"\opt_pr_list-mini.xls" Local $oExcel = _Excel_Open(False, False, False, True)  If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $sPriceFile, True )     ; readonly If @error Then     MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead", "Error opening workbook @error = " & @error & ", @extended = " & @extended)     _Excel_Close($oExcel)     Exit EndIf _Excel_Close($oExcel, False, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 2", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Exit What I do wrong?
    • kctvt
      By kctvt
      Hi there, i'm looking for a script to take max number of a column in Excel.
      Ex :  Column C , i have : 
      (About 134600 rows)

      So, How to know which is the max number in Column C.
      I have this code, but it take me a lot of time >"< 
      So... please help me a faster code.
      $x = 3 $CloseCheck1 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+1) $CloseCheck2 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+2) $CloseCheck3 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+3) $CloseCheck4 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+4) $CloseCheck5 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+5) $CloseCheck6 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+6) $CloseCheck7 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+7) $CloseCheck8 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+8) $CloseCheck9 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+9) $CloseCheck10 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+10) Local $aArray = StringSplit($CloseCheck1&","&$CloseCheck2&","&$CloseCheck3&","&$CloseCheck4&","&$CloseCheck5&","&$CloseCheck6&","&$CloseCheck7&","&$CloseCheck8&","&$CloseCheck9&","&$CloseCheck10,",") $DMAX = _ArrayMax($aArray, 1, 1) $DMIN = _ArrayMin($aArray, 1, 1) $n = 11 While 1 $CloseCheckn = _Excel_RangeRead($oWorkbook, Default, "C"&$x+n) If $CloseCheckn > $DMAX Then Global $DMAX = $CloseCheckn EndIf If $CloseCheckn < $DMIN Then Global $MIN = $CloseCheckn EndIf If $CloseCheckn = "" Then ExitLoop EndIf $n = $n + 1 WEnd _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMAX, "P1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMIN, "P2")  
    • snaileater
      By snaileater
      I'm trying the Excel.udf, my starting point are the examples found in the help.
      My problem is that i can't find any working example of _Excel_RangeInsert ... made many tries but i can't find the reason why ...
      New/existing Worbook nothing changes ... i tried _Excel_RangeWrite without any problem, but with _Excel_RangeInsert i always get an @error=3 and @extended=-2147352562 ...
      What could i be missing ?
      Here's the minimalistic snippet i'm playing with :
      Local $sWorkbook = @ScriptDir & "\pixel.xls" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) $dummy=_Excel_RangeInsert($oWorkbook.Activesheet, "1:3") If @error Then    $a=@extended    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error inserting" & @CRLF & "@error = " & @error & ", @extended = " & @extended) Else    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Rows successfully inserted") EndIf ConsoleWrite ($dummy & chr(13) & $a & Chr(13)) I tried every possible (...) syntax for the range object, without success ... 
      I use the latest releases of AutoIt and Office 97 ...
      Thanks for your help ...
    • PINTO1927
      By PINTO1927
      Hello guys,
      through this example that you find at the end I can not get to write a text in cell A1 example, without being open excel.
      #include <Excel.au3> $oExcel = _Excel_Open(False) $sWorkbook = @DesktopDir & "\test\test.xlsx" $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Test" & @CRLF & "String")  
      while if I make "_Excel_Open(True)" visible, the text is written.