Fixed length values in Array

[drbyte]

Hello All,
 
I'am quite new to Autoit and i'am struggling with an array problem.
I need to fill cells in Excell. (A1:A99)
The length of the "value" is always 7 digits,  like ABCD001 till a "value" of maximum ABCD999.
 
I can fill the array but i'am missing the leading zero's before the numbers
ABCD001 is ABCD1 
ABCD099 is ABCD99
 
How to solve this?
 
I've made the script below.

#include <Array.au3>
Global $FirstNumber = "ABCD001"
Global $amount = "999"

Local $a = StringToASCIIArray($FirstNumber, 0, 4)

Dim $arr[1][$amount + 1]
For $i = 0 To 0
    For $j = 0 To $amount - 1
        $arr[$i][$j] = Chr($i + $a[0]) & Chr($i + $a[1]) & Chr($i + $a[2]) & Chr($i + $a[3]) & ($j + 1)
    Next
Next
;_ArrayDisplay($arr)

$MyExcel = ObjCreate("Excel.Application") ; Create an Excel Object

If @error Then
    MsgBox(0, "", "Error creating Excel object. Error code: " & @error)
    Exit
EndIf

If Not IsObj($MyExcel) Then
    MsgBox(0, "ExcelTest", "I'm sorry, but creation of an Excel object failed.")
    Exit
EndIf
$MyExcel.Visible = 1
$MyExcel.workbooks.add

; Set all values
$MyExcel.activesheet.range("A1:A" & $amount).value = $arr
MsgBox(0, "", "Click 'ok' to clear the cells")
$MyExcel.activeworkbook.saved = 1 ; To prevent 'yes/no' questions from Excel
$MyExcel.quit ; Get rid of him.
$MyExcel = 0 ; Loose this object.
Exit

Thanks in advance

Edited May 20, 2013 by drbyte

[Melba23]

drbyte,

StringFormat is your friend here:

$sAlpha = "ABCD"

For $i = 1 To 999
    $sItem = $sAlpha & StringFormat("%03d", $i)
    ConsoleWrite($sItem & @CRLF)
Next

All clear?

M23

P.S. Welcome to the AutoIt forum.

Edited May 20, 2013 by Melba23

[BrewManNH]

Something like this perhaps?
 

#include <Array.au3>
Global $FirstNumber = "ABCD"
Global $amount = "999"
Local $a = StringToASCIIArray($FirstNumber, 0, 4)
Global $arr[1][$amount + 1]
For $j = 0 To $amount - 1
$arr[0][$j] = $FirstNumber & StringFormat("%03s", $j)
Next
_ArrayDisplay($arr)
$MyExcel = ObjCreate("Excel.Application") ; Create an Excel Object
If @error Then
    MsgBox(0, "", "Error creating Excel object. Error code: " & @error)
    Exit
EndIf
If Not IsObj($MyExcel) Then
    MsgBox(0, "ExcelTest", "I'm sorry, but creation of an Excel object failed.")
    Exit
EndIf
$MyExcel.Visible = 1
$MyExcel.workbooks.add
; Set all values
$MyExcel.activesheet.range("A1:A" & $amount).value = $arr
MsgBox(0, "", "Click 'ok' to clear the cells")
$MyExcel.activeworkbook.saved = 1 ; To prevent 'yes/no' questions from Excel
$MyExcel.quit ; Get rid of him.
$MyExcel = 0 ; Loose this object.
Exit

[drbyte]

Melba23

Your solution works like a charm!.

But when i load them in Array the array starts with ABCD000 instead of ABCD001

BrewManNH

Your solutions also works like a charm but also the Array starts at ABCD000, how to make it start at ABCD001?

Thank you both for the very quick answers !!!!!

#include <Array.au3>
$sAlpha = "ABCD"
Global $amount = "999"

For $i = 1 To $amount
    $sItem = $sAlpha & StringFormat("%03d", $i)
    ConsoleWrite($sItem & @CRLF)
Next

Global $arr[1][$amount + 1]
For $j = 0 To $amount - 1
    $arr[0][$j] = $sAlpha & StringFormat("%03d", $j)
Next
_ArrayDisplay($arr)

[VixinG]

For $j = 1 To $amount - 1

To start from ABCD001.

[BrewManNH]

What would you think you'd need to change to fix the starting from 0 to starting from 1?

Nevermind, some people take away all the fun.

Edited May 20, 2013 by BrewManNH

[VixinG]

  On 5/20/2013 at 7:34 PM, BrewManNH said:

What would you think you'd need to change to fix the starting from 0 to starting from 1?

Nevermind, some people take away all the fun.

Oh.. I feel terrible now 

Sorry, I should give him a tip, not a solution.

[drbyte]

BrewManNH & VixinG

Before i posted the question i alreay tried that  , and indeed then it starts with ABCD001, but then
the first value of the arry is an empty one.

I want to start filling the arry with ABCD001 instead of an empty value, is this possible?

[VixinG]

I found an answer quick, but yeah:
What would you think you'd need to change to fix the starting from Col 1 with ABCD001 instead of Col 0?

Edited May 20, 2013 by VixinG

[drbyte]

VixinG

Working with array's is quite new to me.

Your hint gave me the right solution.

$arr[0][$j-1] = $sAlpha & StringFormat("%03d", $j)

Edited May 20, 2013 by drbyte

[guinness]

  On 5/20/2013 at 7:49 PM, drbyte said:

 

Wayfarer

User name is above in the blue-like bar.

[VixinG]

  On 5/20/2013 at 7:49 PM, drbyte said:

 

Wayfarer

Working with array's is quite new to me.

Your hint gave me the right solution.

$arr[0][$j-1] = $sAlpha & StringFormat("%03d", $j)

Great job, I was about to post two possible solutions to this.

For $j = 1 To $amount - 1
    ;^ here
    $arr[0][$j-1] = $sAlpha & StringFormat("%03d", $j)

Next

; OR

For $j = 0 To $amount - 1

    $arr[0][$j] = $sAlpha & StringFormat("%03d", $j+1) ;here

Next

[drbyte]

There is happening something strange i don't understand.

When i want to start in cell A10 in excel to fill with the array i'am missing values?

See code below.

#include <Array.au3>
Global $FirstNumber = "ABCD"
Global $amount = "20"
Local $a = StringToASCIIArray($FirstNumber, 0, 4)
Global $arr[1][$amount + 1]
For $j = 1 To $amount - 1
$arr[0][$j-1] = $FirstNumber & StringFormat("%03s", $j)
Next
_ArrayDisplay($arr)
$MyExcel = ObjCreate("Excel.Application") ; Create an Excel Object
If @error Then
    MsgBox(0, "", "Error creating Excel object. Error code: " & @error)
    Exit
EndIf
If Not IsObj($MyExcel) Then
    MsgBox(0, "ExcelTest", "I'm sorry, but creation of an Excel object failed.")
    Exit
EndIf
$MyExcel.Visible = 1
$MyExcel.workbooks.add
; Set all values
$MyExcel.activesheet.range("A10:A" & $amount).value = $arr
MsgBox(0, "", "Click 'ok' to clear the cells")
$MyExcel.activeworkbook.saved = 1 ; To prevent 'yes/no' questions from Excel
$MyExcel.quit ; Get rid of him.
$MyExcel = 0 ; Loose this object.
Exit

Edited May 20, 2013 by drbyte

[drbyte]

Solved !!

$MyExcel.activesheet.range("A11:A" & $amount+11).value = $arr