Sign in to follow this  
Followers 0
RickB75

latest version of Autoit How to change Excel Number format

3 posts in this topic

Guys,

      I'm trying to figure out how to change the number format in excel using the latest version of Autoit. The old version I used this 

_ExcelNumberFormat($oExcel, "0.00", "E:E")

I didn't see anything in the help file about it and it's not in the change file. I did find this on MSDN but I'm not sure how to use this in my script. 

How can I format my number column in excel like this 25599.00 from this 25599. I think it's something like this. 

$oExcel.NumberFormat($oExcel, "0.00", "E:E")

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Guys,

      After searching and trying diff method's, this is what worked for me. I copied the function from an older version of Excel.au3 and paste it at the bottom of my script and used the original syntax and it worked. 

BTW: I'm using Excel 2013 and this worked. 

Func _ExcelNumberFormat($oExcel, $sFormat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
        If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
        If $iColStart < 1 Then Return SetError(2, 1, 0)
        If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
        If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
        With $oExcel.ActiveSheet
            .Range(.Cells($sRangeOrRowStart, $iColStart), .Cells($iRowEnd, $iColEnd)).NumberFormat = $sFormat
        EndWith
        Return 1
    Else
        $oExcel.ActiveSheet.Range($sRangeOrRowStart).NumberFormat = $sFormat
        Return 1
    EndIf
EndFunc   ;==>_ExcelNumberFormat
Edited by RickB75

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

I use something like this, personally:

#include <Excel.au3>
 
$oExcel = _Excel_Open()

$oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\1.xls")
$oWorkbook.ActiveSheet.Range("A1:A20").NumberFormat = "hh:mm:ss"
$oWorkbook.ActiveSheet.Range("B1:B20").NumberFormat = "0.00%"
$oWorkbook.ActiveSheet.Columns("C").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Edited by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

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  
Followers 0