Jump to content

Recommended Posts

Posted (edited)

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 by drbyte
  • Moderators
Posted (edited)

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 by Melba23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Posted

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

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 Gude
How 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

Posted

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)
Posted (edited)

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 by BrewManNH

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 Gude
How 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

Posted

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.

[indent=3][/indent]

Posted

BrewManNH & VixinG

Before i posted the question i alreay tried that :geek: , 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?

Posted (edited)

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 by VixinG

[indent=3][/indent]

Posted (edited)

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 by drbyte
Posted

 

Wayfarer

User name is above in the blue-like bar.

UDF List:

 
_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_ArrayFilter/_ArrayReduce_BinaryBin()_CheckMsgBox()_CmdLineRaw()_ContextMenu()_ConvertLHWebColor()/_ConvertSHWebColor()_DesktopDimensions()_DisplayPassword()_DotNet_Load()/_DotNet_Unload()_Fibonacci()_FileCompare()_FileCompareContents()_FileNameByHandle()_FilePrefix/SRE()_FindInFile()_GetBackgroundColor()/_SetBackgroundColor()_GetConrolID()_GetCtrlClass()_GetDirectoryFormat()_GetDriveMediaType()_GetFilename()/_GetFilenameExt()_GetHardwareID()_GetIP()_GetIP_Country()_GetOSLanguage()_GetSavedSource()_GetStringSize()_GetSystemPaths()_GetURLImage()_GIFImage()_GoogleWeather()_GUICtrlCreateGroup()_GUICtrlListBox_CreateArray()_GUICtrlListView_CreateArray()_GUICtrlListView_SaveCSV()_GUICtrlListView_SaveHTML()_GUICtrlListView_SaveTxt()_GUICtrlListView_SaveXML()_GUICtrlMenu_Recent()_GUICtrlMenu_SetItemImage()_GUICtrlTreeView_CreateArray()_GUIDisable()_GUIImageList_SetIconFromHandle()_GUIRegisterMsg()_GUISetIcon()_Icon_Clear()/_Icon_Set()_IdleTime()_InetGet()_InetGetGUI()_InetGetProgress()_IPDetails()_IsFileOlder()_IsGUID()_IsHex()_IsPalindrome()_IsRegKey()_IsStringRegExp()_IsSystemDrive()_IsUPX()_IsValidType()_IsWebColor()_Language()_Log()_MicrosoftInternetConnectivity()_MSDNDataType()_PathFull/GetRelative/Split()_PathSplitEx()_PrintFromArray()_ProgressSetMarquee()_ReDim()_RockPaperScissors()/_RockPaperScissorsLizardSpock()_ScrollingCredits_SelfDelete()_SelfRename()_SelfUpdate()_SendTo()_ShellAll()_ShellFile()_ShellFolder()_SingletonHWID()_SingletonPID()_Startup()_StringCompact()_StringIsValid()_StringRegExpMetaCharacters()_StringReplaceWholeWord()_StringStripChars()_Temperature()_TrialPeriod()_UKToUSDate()/_USToUKDate()_WinAPI_Create_CTL_CODE()_WinAPI_CreateGUID()_WMIDateStringToDate()/_DateToWMIDateString()Au3 script parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 22/04/2018

  • Solution
Posted

 

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

[indent=3][/indent]

Posted (edited)

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 by drbyte

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...