drbyte Posted May 20, 2013 Posted May 20, 2013 (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 May 20, 2013 by drbyte
Moderators Melba23 Posted May 20, 2013 Moderators Posted May 20, 2013 (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 May 20, 2013 by Melba23 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 columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area
BrewManNH Posted May 20, 2013 Posted May 20, 2013 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 GudeHow 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
drbyte Posted May 20, 2013 Author Posted May 20, 2013 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 Posted May 20, 2013 Posted May 20, 2013 For $j = 1 To $amount - 1 To start from ABCD001. [indent=3][/indent]
BrewManNH Posted May 20, 2013 Posted May 20, 2013 (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 May 20, 2013 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 GudeHow 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
VixinG Posted May 20, 2013 Posted May 20, 2013 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]
drbyte Posted May 20, 2013 Author Posted May 20, 2013 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 Posted May 20, 2013 Posted May 20, 2013 (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 May 20, 2013 by VixinG [indent=3][/indent]
drbyte Posted May 20, 2013 Author Posted May 20, 2013 (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 May 20, 2013 by drbyte
guinness Posted May 20, 2013 Posted May 20, 2013 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 parsing • AutoIt Search • AutoIt3 Portable • AutoIt3WrapperToPragma • AutoItWinGetTitle()/AutoItWinSetTitle() • Coding • DirToHTML5 • FileInstallr • FileReadLastChars() • GeoIP database • GUI - Only Close Button • GUI Examples • GUICtrlDeleteImage() • GUICtrlGetBkColor() • GUICtrlGetStyle() • GUIEvents • GUIGetBkColor() • Int_Parse() & Int_TryParse() • IsISBN() • LockFile() • Mapping CtrlIDs • OOP in AutoIt • ParseHeadersToSciTE() • PasswordValid • PasteBin • Posts Per Day • PreExpand • Protect Globals • Queue() • Resource Update • ResourcesEx • SciTE Jump • Settings INI • SHELLHOOK • Shunting-Yard • Signature Creator • Stack() • Stopwatch() • StringAddLF()/StringStripLF() • StringEOLToCRLF() • VSCROLL • WM_COPYDATA • More Examples... Updated: 22/04/2018
Solution VixinG Posted May 20, 2013 Solution Posted May 20, 2013 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]
drbyte Posted May 20, 2013 Author Posted May 20, 2013 (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 May 20, 2013 by drbyte
drbyte Posted May 20, 2013 Author Posted May 20, 2013 Solved !! $MyExcel.activesheet.range("A11:A" & $amount+11).value = $arr VixinG 1
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now