DanielTyrkiel

wow, I must be NOOB LvL 002 if Water is asking me for suggestions

I've added a few lines to the code in first post. I think I have found a quick way to paste a largish table back into excel What do you think?

DUH! I'll try that Thanks Water!

Hi, I have found that the above synthax returns an array of all cells used in the active sheet in excel. The benefit I'm trying to exploit is that the array is loaded in an instant! My question is: Do you know if there is a parameter that transposes the array? As it is, when checking with _ArrayDisplay(), the result is that columns from excel are as rows in the array. It's not a big issue because I can code around it, but it would make it easier here's my code: #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.8.1 Author: Daniel Tyrkiel Script Function: testing fast array loading #ce ---------------------------------------------------------------------------- ; Script Start #include <Excel.au3> #include <Array.au3> ;setting hotkey to terminate the script in case it runs away in a loop HotKeySet("{ESC}", "Terminate") ;navigate to the chase up log: $sFilePath002 = FileOpenDialog("Choose a spreadsheet", @MyDocumentsDir, "All (*.*)") If $sFilePath002 = "" Then Exit ;exit if "cancel" gets pressed $oExcel = _ExcelBookOpen($sFilePath002) If $oExcel = 0 Then MsgBox(0, "", "failed to attach", 2) ;speak up if you can't attach Exit EndIf ;core of the test: Local $aTest = $oExcel.ActiveSheet.UsedRange.Value Local $fIsArray = IsArray($aTest) If $fIsArray = 1 Then _ArrayDisplay($aTest, "") Else MsgBox(0, "", "$aTest is not an array") EndIf Local $oExcelNew = _ExcelBookNew() ;_ExcelWriteSheetFromArray($oExcelNew, $aTest) ;ClipPut("") ;_ArrayToClip($aTest) $oExcelNew.ActiveSheet.Range("A1:I100").Value = $aTest ;Functions_______________________________________________________________________________________________________________________________________ Func Terminate() Exit 0 EndFunc ;==>Terminate

I think I've sorted it myself again... Here's what I've added: $oInput_1 = _IETagNameGetCollection($oIE1, "input", 6) $oInput_2 = _IETagNameGetCollection($oIE1, "input", 7) MsgBox(0, "", $oInput_1.name & @CRLF & $oInput_2.name & @CRLF & " Prompt 2 Value is: " & $oInput_2.value & @CRLF & " Prompt 1 Value is: " & $oInput_1.value) I've used the console output to see the index numbers for the values nevermind...

Hi, this is probably another one of my newbie easy questions. Here's the image of the table/form I'm trying to control. https://docs.google.com/file/d/0ByUpE9M4BFXIMjc5aU1ubklYZG8/edit The goal is to set values of the two date input boxes and click submit. Here's the code I've got so far to identify the DOM elements: #include <IE.au3> Local $oIE1 = _IECreate("http://lomacorvu/corvucgi/cvhtmsrv.exe?file=/Loma/Purchasing/UK_PO_Receipts_by_Period.qry&showprompts=1") Local $tagINPUT = _IETagNameGetCollection($oIE1, "input") For $oInput In $tagINPUT ConsoleWrite($oInput.name & @CRLF) If $oInput.name = "prompt_1" Then $oInput_1 = $oInput ElseIf $oInput.name = "prompt_2" Then $oInput_2 = $oInput Else ContinueLoop EndIf #cs Switch $oInput.name Case "prompt_1" $oInput_1 = $oInput Case "prompt_2" $oInput_2 = $oInput EndSwitch #ce Next MsgBox(0, "", $oInput_1.name & @CRLF & $oInput_2.name & @CRLF & " Prompt 2 Value is: " & $oInput_2.value & @CRLF & " Prompt 1 Value is: " & $oInput_1.value) My problem is that instead of the first input box named: "prompt_1", I get the submit element named "0" I'm not sure what I'm doing wrong. I'm only starting to learn this. regards Daniel Here's the result after running it: https://docs.google.com/open?id=0ByUpE9M4BFXITGRhVHo1M0pzT1U

Cool, I ran it. Got it now. I didn't realise that this function assigned its own variable on the fly. I've been learning to read the descriptions of functions a bit closer and noticing the fine differences. Many thanks!

I've just read this: The Variable will be created automatically with a Local scope, even when MustDeclareVars is on. So it's declared by the loop statement itself... The statement operates on colections or arrays so it must recognise them somehow. I think I'm getting it. _IETagNameAllGetCollection retrieves a collection of sorts and the variable defined between "for" and "in" is used to pass elements of the colection one by one. Have I got it?

Hi, I'm trying to understand how does this work: ; ******************************************************* ; Example 1 - Open a browser with the basic example, get the collection ; of all elements and display the tagname and innerText of each ; ******************************************************* #include <IE.au3> Local $oIE = _IE_Example("basic") Local $oElements = _IETagNameAllGetCollection($oIE) For $oElement In $oElements MsgBox(0, "Element Info", "Tagname: " & $oElement.tagname & @CR & "innerText: " & $oElement.innerText) Next in the "For" loop there is a variable "$oElement" but I can't see it defined anywhere. I don't understand how it works. I have searched the web, this forum, autoit help file and even the IE UDF, but can't find a clear answer. Sorry if it's an easy one, but I can't use it if I don't understand it. regards Daniel

Sorry, being lazy here. Was looking through threads to find a way to resize columns in my script. I used: $ColA = $oExcel.Columns("A");get the column 1 $ColA.ColumnWidth = 40; set width to 40 for the column 1 but modified it for my excel and multiplied it for columns A thro P and it crashed my script So I'll give it up for now, got more important things to do. When I get a moment, I'll get back to making the sheets look nice thanks Sorry for the trouble Govner Dan

This function didn't work for me. Columns got expanded 7 times more than I set it to and then the script crashed

OK, NOW I have done my homework... After re-reading the Excel.au3 file... There is an option in _ExcelReadCell() that you put in after the column number. If you set it to "3" then it reads the text seen by ze humanz... Sorry to be a dafty...

I've just noticed that when I changed the formatting of the entire column to "general" then the ones that show as a date in the array also show as a date in excel. The others - that show as a long string are presented as a 5 digit string of numbers in excel...

Solved: sorry, didn't read the excel UDF properly regards Hi, have you come across the date format being kept after the cell value had been added to the array? I DON'T have a problem with the formatting not being kept, because I simply can cut the long string and re-organize it, but when it saves "properly" then my formula for splitting the long string produces a mangled string of characters (obviously) Here's what I found after running a test script on my big spreadsheet: https://docs.google.com/open?id=0ByUpE9M4BFXIVTJpclFWWGtPNlU sorry for the trouble. I have looked through the forum but haven't come across this problem (but I have been called blind before ) Here's the test code I whipped up to produce the array: #include <Excel.au3> #include <Array.au3> Global $YearandWeek = "all weeks 2012" Global $oExcel $sFilePath002 = @MyDocumentsDir & "\Delivery Planning" & "\" & $YearandWeek & ".xlsx" ;path to open the chase up spreadsheet $oExcel = _ExcelBookOpen($sFilePath002) Local $j ;check which row number is the last one For $j = 1 To 2000 $sAnotherCellValue = _ExcelReadCell($oExcel, $j, 1) $sAnotherCellValue = StringLen($sAnotherCellValue) If $sAnotherCellValue = 0 Then ExitLoop Next ;by the time it's finished $j is row number one below the last filled one Local $i Local $avArray[1] ;create the array to work with Local $date $avArray[0] = "the list" ;populate the first line For $i = 2 To $j - 1 $date = _ExcelReadCell($oExcel, $i, 12) ;read the date cell _ArrayAdd($avArray, $date) ;add date to the array Next _ArrayDisplay($avArray, "dates")

I'm working with a JAVA software that resides in an IE frame. It assigns class instances dynamically. Here's a piece of script that I wrote today to read all visible class instances and storing it into an array. It works if the second instance is different than the first one, but obviously exits the loop when the second one is the same as the first one. What would be a good logic to exit the class reading loop when all visible classes have been read and the first one has been reached? Here's my code: #include <Array.au3> Local $window = WinActivate("http://loma25.loma.co.uk - Live, UK - Loma UK, AUL Environment - Microsoft Internet Explorer", "") ; activate the window we want to check Local $sControl = ControlGetFocus($window, "") ;read the first class instance visible Local $avArray[1] ;create the array to work with $avArray[0] = $sControl ;populate the first line with the first control in view Local $i ;this is just a counter For $i = 1 To 10 ControlSend($window, "", $sControl, "{TAB}") ;tab to the next control in view $sControl = ControlGetFocus($window, "") ;read the control in focus Local $SearchResult = _ArraySearch($avArray, $sControl) ;check if the control we read is the same as the first one If $SearchResult = 0 Then ExitLoop ;exit the loop if it is the same _ArrayAdd($avArray, $sControl) ;add it to the array Next _ArrayDisplay($avArray, "all instances") ;show the result

I've learned something today and therefore I feel obliged to share. Finally I know now how to read the class instance from the creen I'm working with. It is by using ControlGetFocus() function. Here's a code that does the check for me: Local $window = WinActivate("http://loma25.loma.co.uk - Live, UK - Loma UK, AUL Environment - Microsoft Internet Explorer", "") Local $sControl = ControlGetFocus ( $window , "" ) MsgBox(0, "ControlGetFocus Example", "The control that has focus is: " & $sControl) another day, another line of code learnt...

it took me three days to finally get to trying this code and it works great. I will start implementing it to my other scripts today. For the benefit of all other newbies (like myself) using Lotus Notes as their default email client, here's the bare minimum script I have used and it worked for me: #include <date.au3> Global $recipient, $Subject, $bodytext, $attachment, $saveit $recipient = "youremail@gmail.com" $Subject = "test" $bodytext = "more test" $attachment = "C:Documents and Settingsyour.nameMy Documentsscriptsemail test.au3" $saveit = True SendNotesMail($recipient, $Subject, $bodytext, $attachment, $saveit) Func SendNotesMail($recipient, $Subject, $bodytext, $attachment, $saveit) ;Start a $Session to notes $Session = ObjCreate("Notes.NotesSession") ;Get the sessions $UserName and then calculate the mail file name $UserName = $Session.UserName $MailDbName = StringLeft($UserName, 1) & StringRight($UserName, (StringLen($UserName) - StringInStr(1, $UserName, " "))) & ".nsf" ;Open the mail database in notes $Maildb = $Session.GETDATABASE("", $MailDbName) If $Maildb.IsOpen = 1 Then ;Already open for mail Else $Maildb.OPENMAIL() EndIf ;Set up the new mail document $MailDoc = $Maildb.CREATEDOCUMENT $MailDoc.Form = "Memo" $MailDoc.sendto = $recipient $MailDoc.Subject = $Subject $MailDoc.Body = $bodytext $MailDoc.SAVEMESSAGEONSEND = $saveit ;Set up the embedded $Object and $attachment and attach it If $attachment <> "" Then If FileExists(@ScriptDir & "" & $attachment) Then $filename = $attachment $fullpath = @ScriptDir & "" & $attachment ElseIf FileExists($attachment) Then $pos = StringInStr($attachment, "", 0, -1) $filename = StringTrimLeft($attachment, $pos) $fullpath = $attachment Else MsgBox(0, "LoNo Error", "attachment not found") Exit EndIf $AttachME = $MailDoc.CREATERICHTEXTITEM($filename) $EmbedObj = $AttachME.EMBEDOBJECT(1454, "", $fullpath, $filename) ;$MailDoc.CREATERICHTEXTITEM($filename) EndIf ;Send the document $MailDoc.PostedDate = _Now();Gets the mail to appear in the sent items folder $MailDoc.SEND(0, $recipient) $Maildb = "NULL" $MailDoc = "NULL" $AttachME = "NULL" $Session = "NULL" $EmbedObj = "NULL" EndFunc ;==>SendNotesMail To use it: change values of variables defined at the beginning press F5 and enjoy Again, many thanks to Raquien!

WOW, this caught me by surprise! Many thanks More to learn...

Thank you, this worked. Is there an updated help file then? This is the official example from the help file that I based it on: #include Example() Func Example() Local $Button_1, $Button_2, $msg GUICreate("My GUI Button") ; will create a dialog box that when displayed is centered Opt("GUICoordMode", 2) $Button_1 = GUICtrlCreateButton("Run Notepad", 10, 30, 100) $Button_2 = GUICtrlCreateButton("Button Test", 0, -1) GUISetState() ; will display an dialog box with 2 button ; Run the GUI until the dialog is closed While 1 $msg = GUIGetMsg() Select Case $msg = $GUI_EVENT_CLOSE ExitLoop Case $msg = $Button_1 Run('notepad.exe') ; Will Run/Open Notepad Case $msg = $Button_2 MsgBox(0, 'Testing', 'Button 2 was pressed') ; Will demonstrate Button 2 being pressed EndSelect WEnd EndFunc ;==>Example Many thanks Water

SOLVED: I was using switch, which required just $Button1, NOT $nMsg = $Button1 Thanks Water! Hi, This is my first stab at GUI. I'm using KODA. I'm trying to get a button to work so that when I press it, a future function can use the information that I input using the calendar and input lines. The problem I get is that the message box (used only for testing) shows up immediately after starting the script - it doesn't wait for the "case" when the button is pressed. What am I doing wrong? I can't find a straight answer in forums and spent over two hours trying to get it to work. Here's my code: #include #include #include #include #include #include #region ### START Koda GUI section ### Form=C:\Documents and Settings\daniel.tyrkiel\My Documents\scripts\Enter order info.kxf $Form1 = GUICreate("Purchase Order Entry", 353, 364, 192, 124) $Date1 = GUICtrlCreateDate("2012/10/12 07:58:56", 32, 24, 129, 25, $DTS_SHORTDATEFORMAT) $Label1 = GUICtrlCreateLabel("choose delivery date", 200, 24, 102, 17) $eWorksOrder = GUICtrlCreateInput("W053330", 32, 184, 129, 21) ;input for works order number $Label2 = GUICtrlCreateLabel("input works order number", 200, 184, 123, 17) $Customer = GUICtrlCreateInput("TILDA", 32, 224, 129, 21, $ES_UPPERCASE) ;input customer $JobNumber = GUICtrlCreateInput("XR45", 32, 264, 129, 21) ;input machine type and week of completion $Label3 = GUICtrlCreateLabel("input customer name ", 200, 224, 105, 17) $Label4 = GUICtrlCreateLabel("input type machine and week", 200, 264, 143, 17) $Button1 = GUICtrlCreateButton("Start", 32, 312, 97, 25) ;GUICtrlSetOnEvent(-1, $Button1) GUISetState(@SW_SHOW) #endregion ### END Koda GUI section ### ;Local $date1trimmed = StringTrimRight(GUICtrlRead($Date1), 4) ;Local $hndl = MsgBox(0, "Date", $date1trimmed & "12") While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $nMsg = $Button1 MsgBox(0, "", "you pressed me") EndSwitch WEnd

Hi, I have found a workaround for this by placing the timer initialiser before the While loop start. Time stamp is created before the loop starts and if the loop continues for more than 1000 milliseconds, an if function exits the loop. Func _ControlC() ; a function for sending Ctrl+C to copy to clipboard ClipPut("") Send("^c") $clip = ClipGet() Local $begin = TimerInit() While $clip == "" $clip = ClipGet() Local $dif = TimerDiff($begin) If $dif = 1000 Then ExitLoop WEnd EndFunc ;==>_ControlC

Hi Water. I know of all of them. Please bear in mind that I am very new to coding. Autoit is my first language that I'm playing with. What my current goal is is to create crude working tools to alleviate the tons of data entry and manipulation work I have and then delve deeper into creating elegant solutions. I've used the copying function before but there has always been something in the form fields (like 2 spaces) and so it had something to copy. I think that because there is nothing there it hangs in a loop. Therefore I put in the timer functions (never used them before). That wasn't the solution and I am stuck. Hope that explains my intentions and my problem Many thanks for your help Daniel

Hi Water. I'd love to use the _IE functions, but I'm too dumb to connect to the iFrame produced by the Java applet that runs inside IE...

Edit: this is now solved, thank you for your help Hi, I'm trying to pull information from a form cell and paste it into excel. Some cells contain information, but some don't When a cell has no information, my copying function stops possibly hanging in the loop. How can I check what is wrong? Here's the code: #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.8.1 Author: Daniel Tyrkiel Script Function: Copy bin location #ce ---------------------------------------------------------------------------- ; Script Start #include MsgBox(0, "", "Open Linked Item Maintenance") Local $eWorksOrder = InputBox("", "Enter spreadsheet name to open") Local $sFilePath = @MyDocumentsDir & "\ordering lists" & "\" & $eWorksOrder & ".xlsx" _ExcelBookOpen($sFilePath) Local $oExcel = _ExcelBookAttach($sFilePath) ;with Default Settings ($s_mode = "FilePath" ==> Full path to the open workbook) If $oExcel = 0 Then MsgBox(0, "", "failed to attach", 2) Func _ControlC() ; a function for sending Ctrl+C to copy to clipboard ClipPut("") Send("^c") $clip = ClipGet() While $clip == "" $clip = ClipGet() Local $begin = TimerInit() Local $dif = TimerDiff($begin) If $dif = 1000 Then ExitLoop WEnd EndFunc ;==>_ControlC ;check which row number is the last one Local $j = 2 For $j = 2 To 100 Local $sAnotherCellValue = _ExcelReadCell($oExcel, $j, 4) If $sAnotherCellValue = 0 Then ExitLoop Next ;the below is the core of the script For $i = 2 To $j - 1 ;Loop $sCellValue = _ExcelReadCell($oExcel, $i, 4) Local $oLoma = WinActivate("http://loma25.loma.co.uk - Live, UK - Loma UK, AUL Environment - Microsoft Internet Explorer") MouseClick("left", 150, 120, 2, 8) ;clicking into the input line ;Local $len = StringLen($sCellValue) Send($sCellValue) ;pasting the part number copied from excel MouseClick("left", 50, 245, 1, 8) ;clicking on the OK button WinWaitActive("Profile Selection Exists (0/1)") ;wait for the pop up box MouseClick("left", 430, 250, 1, 8) ;clicking into the item stockroom profile field Send("1") ;input 1 into the field MouseClick("left", 440, 560, 1, 8) ;clicking the pop up window OK button WinWaitActive("http://loma25.loma.co.uk - Live, UK - Loma UK, AUL Environment - Microsoft Internet Explorer") ;waiting for the pop up to disappear Local $sIssuingStkrm = _ExcelReadCell($oExcel, $i, 9) Send($sIssuingStkrm) ;pasting the stockroom value Send("{ENTER}") ;clicking OK ;checking if there is a bin number present: MouseClick("left", 609, 569, 2, 8) ;click in the bin location field _ControlC() ;copying the contents Local $resultLength = StringLen(ClipGet()) ;checking the length of the copied string MsgBox(0, "", $resultLength) If $resultLength = 2 Then _ExcelWriteCell($oExcel, "none", $i, 15) ElseIf $resultLength > 2 Then Local $sBinNumber = StringTrimRight(ClipGet(), 2) _ExcelWriteCell($oExcel, $sBinNumber, $i, 15) EndIf WinActivate("http://loma25.loma.co.uk - Live, UK - Loma UK, AUL Environment - Microsoft Internet Explorer") WinWaitActive("http://loma25.loma.co.uk - Live, UK - Loma UK, AUL Environment - Microsoft Internet Explorer") MouseClick("left", 355, 669, 1, 8) ;clicking on the Previous button MouseClick("left", 233, 237, 1, 8) ;clicking on the Restart button MouseClick("left", 620, 732, 1, 8) ;clicking on the pop up Previous button WinWaitActive("Profile Selection Exists (0/1)") ;wait for the pop up box Next

Have you tried requesting the excel file from the statistics office? If this is your country they ought to be able to help you... Just an optimistic thought regards Dan