Atrax27 1 Posted December 4, 2020 Yes it's another array topic! Our favorite. Looking for a way to transfer an excel spreadsheet into some sort of array that AutoIT can run. I’ve tried some different arrays that have been posted earlier in my threads (thanks guys!), but the importance here is that it is a dynamic and changing number of rows each time. One time it may be a single row, another time it may be 30 rows. And each time it needs to perform a certain function between all the rows, which changes ever three rows. Here is an working example which does what I want, but changing this is very difficult as it requires flattening the array first within excel each time I want to run it. expandcollapse popup#include <AutoItConstants.au3> Opt("sendkeydelay", 25) $i = 0 $s = 35 Dim $array[35] = ["Yellow","5","Large","7","Cookies","Orange","6","Small","7","Cakes","Blue","1","Medium","7","Breads","Purple","45","Huge","7","Oysters","Grey","2","Tall","7","Lemons","Green","3","Slow","7","Chocolates","Red","99","Furry","7","Ice Creams"] Run("notepad.exe") WinWaitActive("Untitled - Notepad") Do send($array[$i]) Send(" puppies have over ") $i = $i + 1 send($array[$i]) Send(" very very ") $i = $i + 1 send($array[$i]) Send(" copies of a cool new book. After they eat ") $i = $i + 1 send($array[$i]) Send(" tasty ") $i = $i + 1 send($array[$i]) Send(" they are happy.") Send("{ENTER}") $i = $i + 1 Until $i = 15 Send("{ENTER 5}") Do send($array[$i]) Send(" puppies have over ") $i = $i + 1 send($array[$i]) Send(" very very ") $i = $i + 1 send($array[$i]) Send(" copies of a cool new book. After they eat ") $i = $i + 1 send($array[$i]) Send(" tasty ") $i = $i + 1 send($array[$i]) Send(" they are happy.") Send("{ENTER}") $i = $i + 1 Until $i = 30 Send("{ENTER 5}") Do send($array[$i]) Send(" puppies have over ") $i = $i + 1 send($array[$i]) Send(" very very ") $i = $i + 1 send($array[$i]) Send(" copies of a cool new book. After they eat ") $i = $i + 1 send($array[$i]) Send(" tasty ") $i = $i + 1 send($array[$i]) Send(" they are happy.") Send("{ENTER}") $i = $i + 1 Until $i = $ Until $i = $s Is there a way to possibly just copy-paste an excel table into some sort of “Array processor” which would make my life easier? I know it wouldn’t work in my test configuration, but perhaps what I have is as easy as it’s going to get? Share this post Link to post Share on other sites
GokAy 60 Posted December 4, 2020 (edited) The range you want is: Say, worksheet name is: Sheet1 and table starts at cell "A1" and a header rRange = Worksheets("Sheet1").range("A1").currentregion.offset(1,0).resize(Worksheets("Sheet1").range("A1").currentregion.rows.count - 1,Worksheets("Sheet1").range("A1").currentregion.columns.count) I am not familiar with excel.udf, maybe a function for getting this into a range exists. Edited December 4, 2020 by GokAy Share this post Link to post Share on other sites
water 2,391 Posted December 5, 2020 Yes, it does: _Excel_RangeRead 😃 1 GokAy reacted to this My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
GokAy 60 Posted December 5, 2020 @water Heh, actually a typo there. I know about range exists from reading posts here, however, can you get the range into an array with any simple function? In VBA it would be: Dim arrRange() as variant arrRange = whatever_range Share this post Link to post Share on other sites
Nine 995 Posted December 5, 2020 @GokAy _Excel_RangeRead Success: the data from the specified cell(s). A string for a cell, a zero-based array for a range of cells. 1 GokAy reacted to this Not much of a signature but working on it... Spoiler Block all input without UAC Save/Retrieve Images to/from Text Tool to search content in au3 files Date Range Picker Sudoku Game 2020 Overlapped Named Pipe IPC x64 Bitwise Operations Multi-keyboards HotKeySet Fast and simple WCD IPC Multiple Folder Selector GIF Animation (cached) Share this post Link to post Share on other sites
water 2,391 Posted December 5, 2020 @GokAy Which typo? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
GokAy 60 Posted December 5, 2020 @water 14 hours ago, GokAy said: maybe a function for getting this into a range exists. I meant to ask, "maybe a function for getting this into 'an array' exists" Share this post Link to post Share on other sites
water 2,391 Posted December 5, 2020 I see My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
Atrax27 1 Posted December 6, 2020 (edited) EDIT - see next post. Edited December 6, 2020 by Atrax27 post was overcome by events Share this post Link to post Share on other sites
Atrax27 1 Posted December 6, 2020 It's hilarious, if I copy 3x5 table from excel, it spits out weird formatted stuff based on the fact that there is a pipe (|) between array items, but not at the end of the array. But if I copy 4x5 table from excel, it recognizes the pipes and thus does not spit out hot formatted garbage. The results are still reversed (upside down) and I don't know how to fix that. expandcollapse popup#include <Array.au3> #include <AutoItConstants.au3> Opt("sendkeydelay", 25) Local $sExcelData = ClipGet() $sExcelData = StringTrimRight(StringReplace($sExcelData, @TAB, "|"), 2) Local $aExcel1D = StringSplit($sExcelData, @LF, 2) If IsArray($aExcel1D) Then If StringInStr($sExcelData, "|") Then Local $aExcel2D[0][0] For $i = (UBound($aExcel1D) - 1) To 0 Step - 1 If StringStripWS($aExcel1D[$i], 8) = "" Then ContinueLoop $aExcelLine = StringSplit($aExcel1D[$i], "|", 2) ReDim $aExcel2D[UBound($aExcel2D)][UBound($aExcelLine)] _ArrayAdd($aExcel2D, _ArrayToString($aExcelLine)) Next Else Local $aExcel2D = $aExcel1D EndIf EndIf _ArrayDisplay($aExcel2D) Run("notepad.exe") WinWaitActive("Untitled - Notepad") $i = 0 Do send($aExcel2D[0][$i]) Send(" color. ") send($aExcel2D[1][$i]) Send(" quantity. ") send($aExcel2D[2][$i]) Send(" size. ") send($aExcel2D[3][$i]) Send(" number. ") send($aExcel2D[4][$i]) Send(" treat.") Sleep(500) Send("{ENTER}") $i = $i + 1 Until $i = 3 Copy this Yellow Orange Blue 5 6 1 Large Small Medium 7 7 7 Cookie Cake Bread gives you this Quote Cookie color. 7 quantity. Large size. 5 number. Yellow treat. Cake color. 7 quantity. Small size. 6 number. Orange treat. Bread color. 7 quantity. Medium size. 1 number. Blue treat. But copy this Yellow Orange Blue Purple 5 6 1 45 Large Small Medium Huge 7 7 7 7 Cookie Cake Bread Oysters Gives you this Quote Cookie color. 7 quantity. Large size. 5 number. Yellow treat. Cake color. 7 quantity. Small size. 6 number. Orange treat. Bread color. 7 quantity. Medium size. 1 number. Blue treat. Share this post Link to post Share on other sites
Nine 995 Posted December 6, 2020 Here : #include <Array.au3> #include <AutoItConstants.au3> Opt("sendkeydelay", 25) Local $sExcelData = ClipGet() MsgBox ($MB_SYSTEMMODAL,"",$sExcelData) StringReplace(StringMid($sExcelData, 1, StringInStr($sExcelData,@CRLF)), @TAB, @TAB) Local $aExcel2D[0][@extended+1] _ArrayAdd($aExcel2D, $sExcelData, 0, @TAB, @CRLF) _ArrayDisplay($aExcel2D) Run("notepad.exe") WinWaitActive("[CLASS:Notepad]") For $i = 0 To UBound($aExcel2D, 2) Send($aExcel2D[0][$i]) Send(" color. ") send($aExcel2D[1][$i]) Send(" quantity. ") send($aExcel2D[2][$i]) Send(" size. ") send($aExcel2D[3][$i]) Send(" number. ") send($aExcel2D[4][$i]) Send(" treat.") Send("{ENTER}") Next That works for both of your examples 1 Atrax27 reacted to this Not much of a signature but working on it... Spoiler Block all input without UAC Save/Retrieve Images to/from Text Tool to search content in au3 files Date Range Picker Sudoku Game 2020 Overlapped Named Pipe IPC x64 Bitwise Operations Multi-keyboards HotKeySet Fast and simple WCD IPC Multiple Folder Selector GIF Animation (cached) Share this post Link to post Share on other sites
Atrax27 1 Posted December 6, 2020 @Nine Wow. Really nice 🤤. One last question I think on this one expandcollapse popup#include <Array.au3> #include <AutoItConstants.au3> Opt("sendkeydelay", 20) Local $sExcelData = ClipGet() MsgBox ($MB_SYSTEMMODAL,"",$sExcelData) StringReplace(StringMid($sExcelData, 1, StringInStr($sExcelData,@CRLF)), @TAB, @TAB) Local $aExcel2D[0][@extended+1] _ArrayAdd($aExcel2D, $sExcelData, 0, @TAB, @CRLF) _ArrayDisplay($aExcel2D) Run("notepad.exe") WinWaitActive("[CLASS:Notepad]") ;For $i = 0 To UBound($aExcel2D, 2) === THIS is amazing For $i = 0 To 4 Send($aExcel2D[$i][0]) Send(" color. ") send($aExcel2D[$i][1]) Send(" quantity. ") send($aExcel2D[$i][2]) Send(" size. ") send($aExcel2D[$i][3]) Send(" number. ") send($aExcel2D[$i][4]) Send(" treat.") Send("{ENTER}") Next Send("{ENTER 4}") For $i = 5 To 10 Send($aExcel2D[$i][0]) Send(" color. ") send($aExcel2D[$i][1]) Send(" quantity. ") send($aExcel2D[$i][2]) Send(" size. ") send($aExcel2D[$i][3]) Send(" number. ") send($aExcel2D[$i][4]) Send(" treat.") Send("{ENTER}") Next Send("{ENTER 4}") For $i = 11 to 16 Send($aExcel2D[$i][0]) Send(" color. ") send($aExcel2D[$i][1]) Send(" quantity. ") send($aExcel2D[$i][2]) Send(" size. ") send($aExcel2D[$i][3]) Send(" number. ") send($aExcel2D[$i][4]) Send(" treat.") Send("{ENTER}") Next Using this table Yellow 5 Large 7 Cookie Orange 6 Small 7 Cake Blue 1 Medium 7 Bread Purple 45 Huge 7 Oysters Grey 2 Tiny 7 Lemons Green 3 Massive 7 Chocolate Red 99 Big 7 Ice Cream Yellow 5 Large 7 Cookie Orange 6 Small 7 Cake Blue 1 Medium 7 Bread Purple 45 Huge 7 Oysters Grey 2 Tiny 7 Lemons Green 3 Massive 7 Chocolate Red 99 Big 7 Ice Cream Leaves you with a single blank at the very end. Any idea why or how to get rid of that last item? Quote color. quantity. size. number. treat. Share this post Link to post Share on other sites
Nine 995 Posted December 6, 2020 Oh, I did not copy last @CRLF. In your original you were stripping last 2 chars. Not much of a signature but working on it... Spoiler Block all input without UAC Save/Retrieve Images to/from Text Tool to search content in au3 files Date Range Picker Sudoku Game 2020 Overlapped Named Pipe IPC x64 Bitwise Operations Multi-keyboards HotKeySet Fast and simple WCD IPC Multiple Folder Selector GIF Animation (cached) Share this post Link to post Share on other sites
Atrax27 1 Posted December 6, 2020 Sorry about that, I changed it around a bit I know. How would you fix the code it to accommodate the newer format? Share this post Link to post Share on other sites
Nine 995 Posted December 6, 2020 Like I said just strip last 2 chars : Local $sExcelData = StringTrimRight(ClipGet(),2) 1 Atrax27 reacted to this Not much of a signature but working on it... Spoiler Block all input without UAC Save/Retrieve Images to/from Text Tool to search content in au3 files Date Range Picker Sudoku Game 2020 Overlapped Named Pipe IPC x64 Bitwise Operations Multi-keyboards HotKeySet Fast and simple WCD IPC Multiple Folder Selector GIF Animation (cached) Share this post Link to post Share on other sites
Atrax27 1 Posted December 6, 2020 That worked PERFECT thank you. But now I'm curious about what two characters are getting stripped from the array? Is it a ghost carriage return or "end of array" character? Share this post Link to post Share on other sites
Nine 995 Posted December 6, 2020 It is a @CRLF (@Cr & @LF) 1 Atrax27 reacted to this Not much of a signature but working on it... Spoiler Block all input without UAC Save/Retrieve Images to/from Text Tool to search content in au3 files Date Range Picker Sudoku Game 2020 Overlapped Named Pipe IPC x64 Bitwise Operations Multi-keyboards HotKeySet Fast and simple WCD IPC Multiple Folder Selector GIF Animation (cached) Share this post Link to post Share on other sites
Atrax27 1 Posted December 6, 2020 Learn something new everyday. Seems so trivial and obvious but never thought of it before, kinda like "oh, the air I'm breathing actually has O2 in it??" Thanks again. Share this post Link to post Share on other sites
Nine 995 Posted December 6, 2020 Not much of a signature but working on it... Spoiler Block all input without UAC Save/Retrieve Images to/from Text Tool to search content in au3 files Date Range Picker Sudoku Game 2020 Overlapped Named Pipe IPC x64 Bitwise Operations Multi-keyboards HotKeySet Fast and simple WCD IPC Multiple Folder Selector GIF Animation (cached) Share this post Link to post Share on other sites