We help IT Professionals succeed at work.

AutoHotKey SetFormat,Float,6.2 not working on currency in Excel

Cant SetFormat,Float,6.2 on a number formatted as currency in excel. If its formatted as text and not currency everything works fine.

Is there a way to add a decimal and 2 trailing digits some other way? I always want two trailing digits even if they are zeros.
#Warn,UseUnsetLocal
#NoEnv
#SingleInstance Force
SetBatchLines,-1

SourceFile:="C:\Documents and Settings\Administrator\Desktop\Sample.xlsx"
InputBox, DepNum, Deposit Number, Enter the Deposit Number you would like to process
NumberColumns:=9
NumberRows:=30

Output:=""
ColumnLetters:=["A","B","C","D","E","F","G","H","I"]
oWorkbook:=ComObjGet(SourceFile)
oExcel.Sheets(DepNum).Select
ExcelCells:=Object()
Loop,%NumberRows%
{
  If (A_Index=1 or A_Index=2)
    Continue ; ignore first row
  CurrentRow:=A_Index
  Loop,%NumberColumns%
  {
    If (A_Index=1)
      Continue ; ignore column A
    If (A_Index=6)
      SetFormat,Float,6.2
    If (A_Index=7 or A_Index=9)
      SetFormat,Float,6.0
    CurrentColumn:=ColumnLetters[A_Index]
    CurrentCell:=CurrentColumn . CurrentRow
    ExcelCells[CurrentColumn,CurrentRow]:=oWorkbook.Worksheets(DepNum).Range(CurrentCell).Value
    If (A_Index=2)
      Output:=Output . "10" . ExcelCells[CurrentColumn,CurrentRow] . " - "
    Else
      Output:=Output . ExcelCells[CurrentColumn,CurrentRow] . " - "
    If (A_Index=9)
      StringTrimRight,Output,Output,3
  }
  Output:=Output . "`n"
}
MsgBox, 3, Correct?, Is this the information You would like to process? (press Yes or No or Cancel to quit) `n%Output%
IfMsgBox Yes
    MsgBox You pressed Yes.
IfMsgBox No
	MsgBox You pressed No.
else
    MsgBox You pressed Cancel.
ExitApp

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Joe WinogradDeveloper
Fellow 2017
Most Valuable Expert 2018

Commented:
Hi Stuart,
The SetFormat command affects AutoHotkey variables. If I'm understanding you right, you want to affect the Excel cells. For that, you'll need to use the NumberFormat property on the Excel range that you want to affect. For example:

oExcel.Range(CurrentCell).NumberFormat:="#,##0.00"

Open in new window

That will make a number in the cell with the thousands separator (a comma, of course) and two digits after the decimal point. Regards, Joe

Author

Commented:
Hello again Joe,
Thanks for your reply.

I actually am referring to the variable specifically line 26.

I assume the answer is different in that case?

Author

Commented:
I dont even know what I was saying its not a variable but it is line 26
Joe WinogradDeveloper
Fellow 2017
Most Valuable Expert 2018

Commented:
OK, I'm officially confused. :) Let's start with a simple Yes or No question — do you want the Excel spreadsheet to have numbers in the cells that always have two digits after the decimal point?

Author

Commented:
That would be one solution although I think I'd rather just leave the spread sheets alone.

My goal is to get the decimal and two trailing digits in the MsgBox created in line 41.

For some reason when the cells are formatted as text this happens but when formatted as currency it doesn't.

Thanks for bearing with me.
Developer
Fellow 2017
Most Valuable Expert 2018
Commented:
OK, now I understand. First, unless you want padding with blanks or zeroes, your AutoHotkey script should have this SetFormat command:
SetFormat,Float,0.2

This is explained in the doc:
https://www.autohotkey.com/docs/commands/SetFormat.htm

Second, you need to put the format into effect by using an assignment statement with the variable. For example:
var:=var+0

Run this script and you'll see what I mean:

SetFormat,Float,0.2
x:=1.2
msgbox % x ; shows 1.2
x:=x+0
msgbox % x ; shows 1.20
exitapp

Open in new window

Regards, Joe

Edit: You're already good on the assignment statement because of line 31.