Atrax27

Since I dont actually care the order of columns, only that they are the same between tabs. Here is a good VBA that works well. Sub SortLTable() Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Sort Key1:=Range(Selection, Selection.End(xlDown)), Order1:=xlAscending, Orientation:=xlLeftToRight End Sub Kinda bummed that I don't yet understand AutoIT well enough yet, since theres likely a better/faster solution within a UDF somewhere, I'm just not proficient enough yet

Splitting out data within a single workbook is relatively simple in VBA, but I'm going to start looking into autoIT for my solutions as it involves multiple .xlsx files. The below code is brought to you by none other than the famous @water dug up from an old thread. #include <Excel.au3> #include <File.au3> Global $aWorkbooks[] = ["C:\Users\exceltest\Book1.xlsx", "C:\Users\exceltest\Book2.xlsx", _ "C:\Users\exceltest\Book3.xlsx", "C:\Users\exceltest\Book4.xlsx"] Global $sDrive, $sDir, $sFileName, $sExtension $oExcel = _Excel_Open() $oWorkbookOut = _Excel_BookNew($oExcel, 1) For $i = 0 To UBound($aWorkbooks, 1) - 1 $oWorkbookIn = _Excel_BookOpen($oExcel, $aWorkbooks[$i], True) $oSheet = _Excel_SheetCopyMove($oWorkbookIn, 1, $oWorkbookOut, $i + 1) _Pathsplit($aWorkbooks[$i], $sDrive, $sDir, $sFileName, $sExtension) $oSheet.Name = $sFileName _Excel_BookClose($oWorkbookIn) Next This is very useful combining multiple files into a single workbook with multiple tabs (one tab per file). But what if you wanted all those resulting tabs to be combined into one "master" tab? VBA code below Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work with object variables Dim sht As Worksheet 'Object for handling worksheets in loop Dim trg As Worksheet 'Master Worksheet Dim rng As Range 'Range object Dim colCount As Integer 'Column count in tables in the worksheets Set wrk = ActiveWorkbook 'Working in active workbook For Each sht In wrk.Worksheets If sht.Name = "Master" Then MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _ "Please remove or rename this worksheet since 'Master' would be" & _ "the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error" Exit Sub End If Next sht 'We don't want screen updating Application.ScreenUpdating = False 'Add new worksheet as the last worksheet Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count)) 'Rename the new worksheet trg.Name = "Master" 'Get column headers from the first worksheet 'Column count first Set sht = wrk.Worksheets(1) colCount = sht.Cells(1, 255).End(xlToLeft).Column 'Now retrieve headers, no copy&paste needed With trg.Cells(1, 1).Resize(1, colCount) .Value = sht.Cells(1, 1).Resize(1, colCount).Value 'Set font as bold .Font.Bold = True End With 'We can start loop For Each sht In wrk.Worksheets 'If worksheet in loop is the last one, stop execution (it is Master worksheet) If sht.Index = wrk.Worksheets.Count Then Exit For End If 'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 'Put data into the Master worksheet trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value Next sht 'Fit the columns in Master worksheet trg.Columns.AutoFit 'Screen updating should be activated Application.ScreenUpdating = True End Sub One problem with the VBA code above is that it ignores when columns are not aligned with eachother, such as: TAB 1 Column 1 Column 2 Column 3 Column 4 ff gg hh jj tt yy uu ii TAB 2 Column 3 Column 4 Column 1 Column 2 11 22 33 44 55 66 77 88 RESULTS Column 1 Column 2 Column 3 Column 4 ff gg hh jj tt yy uu ii 11 22 33 44 55 66 77 88 but what I'd like is: Column 1 Column 2 Column 3 Column 4 ff gg hh jj tt yy uu ii 33 44 11 22 77 88 55 66 I've read through the UDF and am getting closer to understanding it but have some difficulties with this one. Thanks.

HotKeySet("{F1}", "RunFunc") #Region runfunc While (1) Sleep(100) WEnd Func RunFunc() Local $aFuncs[3] = [camera1, camera2, camera3] $aFuncs[Random(0, 2, 1)]() EndFunc ;==>RunFunc #endregion #Region camera1 Func camera1() MsgBox(0, 0, "camera1") EndFunc ;==>camera1 #endregion #Region camera2 Func camera2() MsgBox(0, 0, "camera2") EndFunc ;==>camera2 #endregion #Region camera3 Func camera3() MsgBox(0, 0, "camera3") EndFunc ;==>camera3 #endregion

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.

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?

Sorry about that, I changed it around a bit I know. How would you fix the code it to accommodate the newer format?

@Nine Wow. Really nice 🤤. One last question I think on this one #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?

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. #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 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

EDIT - see next post.

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. #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?

Definitely gonna look into incorporating this method somehow, looks promising. Do you possibly have something like a test "array" au3 that I could play around with? No matter how many times I use the ones in the helpfile I can never seem to glean anything meaningful from them, it's just all so esoteric that I can't make the connection in my mind with what an array does and how it can be used in a way I want to. Take for example the "_ArrayDisplay" helpfile subject example script. It just cycles through a few different visualizations of data but I don't know how to extract that information in a meaningful way (i.e. Send("Array_cell_C3_from_2D_array"))

This is fantastic and works a charm. Thank you very much

So I'm playing around with GUIs and would like to have alternate text display within the GUI itself, depending on what selection is made in a dropdown. I'd like the text "Red Shoes" to display differently depending on the selection. I.E. Frankie has red shoes, Robert has blue shoes, Jill has purple shoes, and so on. I know the color of each person's shoe, but perhaps this gets into arrays and can be expanded to other items (Robert has blue shoes AND green pants AND a tophat). But arrays are not something that make sense to me at the moment and I am suffering at the hands of the helpfile. I'd love to learn if arrays can help me here but don't know how to start testing easy items. #include <ButtonConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> $Form = GUICreate("Neato 5000", 615, 100, 190, 122) $Input = GUICtrlCreateCombo("Frankie", 0, 0, 609, 21) GUICtrlSetData($Input, "Robert|Jill|Jack|Buddy|Holly|Gerald|Charles", "Frankie") $Button = GUICtrlCreateButton("Message", 0, 24, 609, 25) $Label1 = GUICtrlCreateLabel("Red Shoes", 8, 75, 100, 25) GUISetState(@SW_SHOW) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button Example(GUICtrlRead($Input)) EndSwitch WEnd Func Example($text) Msgbox(0,"", "Bye Bye " & GUICtrlRead($Input)) Exit EndFunc I've come across some ways to change this text, but don't know how to implement when it's more than two choices (see below example) GuiCtrlSetData($label2, $stop ? "Present" : "Not Present")

OK I have it working (_OL_ItemSave) but its only working on a single email. What is the preferred method to loop through all emails in a particular folder?

I see, thanks. I'll try to figure out how to iteratively loop through, unless you quickly know a method? It looks as though the saving action is hard coded as "attachment2.jpg" on line 17 of OL_ItemAttachmentSave, how would you open it up to save the attachment as-is?

Just started dipping my toes in the outlook UDF created by water and wooltown, thanks guys! I've been experimenting with the different example scripts and in some cases can get things to work but I don't know how to expand functionality very easily. For example, I'm running the _OL_ItemAttachmentSave function and it saves the single test picture without issue. As soon as I move another email into that test environment folder (Outlook-UDF-test) and run the script it ignores the second email with attachment. Really I'm looking to "save all email attachments into a folder" but am stuck at square one. OL_ItemSave doesn't appear to work Neither does OL_ItemAttachmentGet Thank you.

Thanks GokAy, I think because I pull from so many different spreadsheets and different files, adding that data into an interim spreadsheet would just gum up everything, but I do appreciate your help thank you.

Added a little functionality that greatly helps my particular workflow. I'm just so happy you guys 1) opens the "Array.txt" file 2) I paste my new list (directly from excel) into this text file 3) I save the file and close it 4) Boom, magic, done. I'm in love. #include <Constants.au3> #include <Array.au3> Opt("WinTitleMatchMode",2) ShellExecute("Notepad.exe", @scriptDir & "\ARRAY.txt") Sleep(500) WinWaitClose("ARRAY.txt") $_FilePath = @scriptDir & "\ARRAY.txt" $_FileRead = FileReadToArray ($_FilePath) ;_ArrayDisplay($_FileRead) $Txt = "Dim $array[" & UBound($_FileRead) & "] = [" & '"' & _ArrayToString($_FileRead,CHR(34)&","&CHR(34)) & '"]' ClipPut($Txt) MsgBox($MB_SYSTEMMODAL, "", $Txt)

Nine, can I be your apprentice? EDIT: thank you everyone for your pointers and help here, I really appreciate this forum and everyone here who contributes #include <Constants.au3> #include <Array.au3> #include <File.au3> $_FilePath = 'D:\text.txt' Local $iCountLines = _FileCountLines($_FilePath) $_FileRead = FileReadToArray ($_FilePath) ;_ArrayDisplay($_FileRead) $Txt = "Dim $array[" & $iCountLines & "] = [" & '"' & _ArrayToString($_FileRead,CHR(34)&","&CHR(34)) & '"]' ClipPut($Txt) MsgBox($MB_SYSTEMMODAL, "", $Txt) I think theres probably a way to count the number (12) without using the _FileCountLines function, but I can't easily see it.

I'll be honest I don't yet have a need for the type of array this produces. 2D arrays? Splits on full delimiter? I'm 100% sure I may use them one day but it's beyond my skill at the moment hence why I'm just using a list type array (would that be a 1D array? 😆 ) cause it's simple and I'm not yet smart enough to see a use case for anything else.

Guys I'm so close..... #include <MsgBoxConstants.au3> #include <StringConstants.au3> #include <String.au3> #include <File.au3> $_FilePath = 'D:\text.txt' $_FileRead = FileRead ( $_FilePath ) Local $iCountLines = _FileCountLines($_FilePath) ; Retrieve the number of lines in the current script. Local $sString = StringStripWS($_FileRead, $STR_STRIPALL); Strip all chars in between the words. Local $Txt = "Dim $array[" & $iCountLines & "] = [" & $sString & "]" ClipPut($Txt) MsgBox($MB_SYSTEMMODAL, "", $Txt); may not need this but here just so I can quickly see what it's showing me Now I just need it to replace all "Line Feeds" with a "," and then just put a single quotation at the beginning and end of the array. EDIT: for the text.txt file (if testing), just throw a bunch of garbage in there to see what it does grtey4yy6 yu y5 y65 564u78365 7585853 735 65trehrtehrte rtj67777 00000000000 -------- 78oi78o returns Dim $array[12] = [grtey4yy6yuy5y65564u78365758585373565trehrtehrtertj6777700000000000--------78oi78o] but I'm looking for it to return instead, Dim $array[12] = ["grtey4yy6","yu","y5","y65","564u78365","7585853","735","65trehrtehrte","rtj67777","00000000000","--------","78oi78o]

Different sources and different data runs that I receive. The code is a little more complex than the notepad function I pasted above but it operates the same way. 1) Get a list of items from another source, format is excel spreadsheet and length changes constantly. Just plain text data. 2) I take this plain text data and format it within excel (step 3 in the OP) and then also make it a single line 3) plug list into my au3 script 4) run the script (open website, login, go to form field, type first item in array, hit enter, type second item in array, hit enter, type third item in array, hit enter, etc... So it's a file, but there is a lot of additional info I don't need, and what I do need is often in different locations throughout the excel spreadsheet, or I need to filter data down to get the list I want. In my mind the perfect solution would be an autoit script that prompted me to paste a list of items into a GUI box (unlimited length), I would click "process", and it would spit out my data perfectly formatted in the way I need to run my script. My current solution (step 2) works, but not very efficiently.

This is very cool but it's the reverse of what I'm looking for I think? It creates a nice text file with the list of names and I like how it seems to immediately process everything. I'll definitely use this later for other functionality thank you.

Its not this list of 20 that I specifically care about, it's any list at all. If I have a list of 300 names I want it in the correct format. If I have a list of 5 names I also want it in that format. Step 1) Have a list of items Apple Orange Salmon Chainsaw Step 2) ???? Step 3) Horray it's formatted in an array so it can be utilized in autoit Dim $array[4] = ["Apple","Orange","Salmon","Chainsaw"] Step 4) Run my script Step 5) rinse and repeat at step 1 ad nauseum with a new list of items. Sometimes 10 items, sometimes 300 items. Step 2 is my heartache.

Im not sure how to incorporate that code with a list of text. Below is the end result of what I desire it to do. Its getting to the below point that is my difficulty. Run("notepad.exe") WinWaitActive("Untitled - Notepad") $i = 0 $s = 20 Dim $array[20] = ["Liam","Olivia","Noah","Emma","Oliver","Ava","William","Sophia","Elijah","Isabella","James","Charlotte","Benjamin","Amelia","Lucas","Mia","Mason","Harper","Ethan","Evelyn"] Do send($array[$i]) send("{ENTER}") $i = $i + 1 Until $i = $s