DJChronik Posted July 31, 2008 Share Posted July 31, 2008 Here is actually the whole Code, but nothing very tricky. Focus on the 2 problems i have... This program takes an excel spreadsheet with a Partnumber in the first column, and qty on the next 7 columns to the right. It saves it to an Array, than goes in the other program i use and pastes the qtys. First problem : Everytime it pastes a qty (and even the Partnumber) in my other program , i have to do 2 x backspace because it creates like a new line. (refer to the part at the end starting with For $k = 0 to $nb_pns-1 Step 1 ) Second problem: Sometimes some Partnumbers doesnt have any qty at all, so there is no need to enter nothing in the IBM Planification program... Therefore i wanted to skip so i put : If $pnqty[$k] > 0 Then (there is a pnqty counter in the " FOR $J " section $pnqty[$i] = $pnqty[$i] + $qty[$i] [$j]) Please help expandcollapse popup$begin = TimerInit() Dim $nb_pns, $dummy, $dummy2, $minutes, $hours, $seconds, $p $pnupdated = 0 $nb_pns = InputBox("Setup", "How many PNs?", "", "") If $nb_pns > 0 Then Dim $qty[$nb_pns] [7] Dim $pn[$nb_pns] Dim $pnqty[$nb_pns] Dim $tedit[7] $tedit[0] = "[CLASSNN:IRIS.tedit42]" $tedit[1] = "[CLASSNN:IRIS.tedit43]" $tedit[2] = "[CLASSNN:IRIS.tedit44]" $tedit[3] = "[CLASSNN:IRIS.tedit45]" $tedit[4] = "[CLASSNN:IRIS.tedit46]" $tedit[5] = "[CLASSNN:IRIS.tedit47]" $tedit[6] = "[CLASSNN:IRIS.tedit48]" WinActivate("Microsoft Excel - Relaches SLC.xls") WinWaitActive("Microsoft Excel - Relaches SLC.xls") MouseClick("left",68, 171, 1) Sleep(200) FOR $i = 0 to $nb_pns-1 Step 1 WinWaitActive("Microsoft Excel - Relaches SLC.xls") Send("^c") $pn[$i] = ClipGet() Sleep(50) For $j = 0 to 6 Step 1 Send("{RIGHT}") Send("^c") $qty[$i] [$j] = ClipGet() $pnqty[$i] = $pnqty[$i] + $qty[$i] [$j] Sleep(50) Next Send("{LEFT 7}") Send("{DOWN}") Next WinActivate("IBM Planification - IBM Lotus Notes") WinWaitActive("IBM Planification - IBM Lotus Notes") For $k = 0 to $nb_pns-1 Step 1 If $pnqty[$k] > 0 Then ControlClick("", "", "[CLASSNN:IRIS.tedit6]") Send("{HOME}") Send("+{END}") Send("{Del}") Send($pn[$k]) Send("{BS 2}") MouseClick("left",488, 331, 1) Sleep(1000) MouseClick("left",90, 540, 1) Sleep(1000) For $l = 0 to 6 Step 1 ControlClick("", "", $tedit[$l]) Send("{Del 6}") Send("{BS 6}") Send($qty[$k] [$l]) Send("{BS}") Next MouseClick("left",95, 570, 1) Sleep(1800) $pnupdated = $pnupdated + 1 Else EndIf Next Else MsgBox(4096, "Test", "Must enter a positive number, please retry") EndIf $dummy = TimerDiff($begin)/1000 $dummy2= mod ($dummy, 3600) $hours = ($dummy-$dummy2) / 3600 $dummy = $dummy2 $dummy2= mod ($dummy, 60) $minutes = ($dummy-$dummy2) / 60 $dummy = $dummy2 $seconds= round($dummy) MsgBox(4096, "Operation successful", "Successfully updated "& $pnupdated & " out of " & $nb_pns & " partnumbers!"& @CRLF & @CRLF & "Total process time is "& $hours & " hours, " & $minutes & " minutes and " & $seconds & " seconds." & @CRLF & $pnupdated & $pnqty[0] & $pnqty[1] & $pnqty[2] & $pnqty[3] & " yo!") Thanks! Link to comment Share on other sites More sharing options...
Zedna Posted July 31, 2008 Share Posted July 31, 2008 Look at Excel UDF in latest AutoIt _ExcelReadCell _ExcelReadArray _ExcelReadSheetToArray Resources UDF Â ResourcesEx UDF Â AutoIt Forum Search Link to comment Share on other sites More sharing options...
DJChronik Posted July 31, 2008 Author Share Posted July 31, 2008 Look at Excel UDF in latest AutoIt_ExcelReadCell_ExcelReadArray_ExcelReadSheetToArrayI have 3.2.12.1 and i dont see those anywhere?! Link to comment Share on other sites More sharing options...
Zedna Posted July 31, 2008 Share Posted July 31, 2008 I have 3.2.12.1 and i dont see those anywhere?!Look at latest beta. Resources UDF Â ResourcesEx UDF Â AutoIt Forum Search Link to comment Share on other sites More sharing options...
DJChronik Posted August 1, 2008 Author Share Posted August 1, 2008 Look at latest beta. i changed the code a bit but it stills doesnt work. _Arraydisplay doesnt show, and i get an error at line 31 which is $qty[$j] = _ExcelReadArray($oExcel, $j+1, 2, 7) I'm trying to build an array with $pn and to build several arrays for qty... anyway try figuring out its pretty easy to understand Thanks expandcollapse popup#Include <Excel.au3> #Include <Array.au3> $begin = TimerInit() Dim $nb_pns, $dummy, $dummy2, $minutes, $hours, $seconds, $p $pnupdated = 0 $sFilePath1 = @ScriptDir & "\SLC2.xls" $oExcel = _ExcelBookOpen($sFilePath1) If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist !") Exit EndIf $nb_pns = InputBox("Setup", "How many PNs?", "", "") If $nb_pns > 0 Then Dim $qty[$nb_pns] [7] Dim $pn[$nb_pns] Dim $pnqty[$nb_pns] Dim $tedit[7] $tedit[0] = "[CLASSNN:IRIS.tedit42]" $tedit[1] = "[CLASSNN:IRIS.tedit43]" $tedit[2] = "[CLASSNN:IRIS.tedit44]" $tedit[3] = "[CLASSNN:IRIS.tedit45]" $tedit[4] = "[CLASSNN:IRIS.tedit46]" $tedit[5] = "[CLASSNN:IRIS.tedit47]" $tedit[6] = "[CLASSNN:IRIS.tedit48]" $pn = _ExcelReadArray($oExcel, 1, 1, $nb_pns, 1) _ArrayDisplay( $pn, "Vertical") For $j = 0 to $nb_pns-1 Step 1 $qty[$j] = _ExcelReadArray($oExcel, $j+1, 2, 7) Sleep(50) Next _ArrayDisplay( $qty, "Qtys") _ExcelBookClose($oExcel) WinActivate("IBM Planification - IBM Lotus Notes") WinWaitActive("IBM Planification - IBM Lotus Notes") For $k = 0 to $nb_pns-1 Step 1 ControlClick("", "", "[CLASSNN:IRIS.tedit6]") Send("{HOME}") Send("+{END}") Send("{Del}") Send($pn[$k]) Send("{BS 2}") MouseClick("left",488, 331, 1) Sleep(1000) MouseClick("left",90, 540, 1) Sleep(1000) For $l = 0 to 6 Step 1 ControlClick("", "", $tedit[$l]) Send("{Del 6}") Send("{BS 6}") Send($qty[$k] [$l]) Send("{BS}") Next MouseClick("left",95, 570, 1) Sleep(1800) $pnupdated = $pnupdated + 1 Next Else MsgBox(4096, "Test", "Must enter a positive numerical number, please retry") EndIf $dummy = TimerDiff($begin)/1000 $dummy2= mod ($dummy, 3600) $hours = ($dummy-$dummy2) / 3600 $dummy = $dummy2 $dummy2= mod ($dummy, 60) $minutes = ($dummy-$dummy2) / 60 $dummy = $dummy2 $seconds= round($dummy) MsgBox(4096, "Operation successful", "Successfully updated "& $pnupdated & " out of " & $nb_pns & " partnumbers!"& @CRLF & @CRLF & "Total process time is "& $hours & " hours, " & $minutes & " minutes and " & $seconds & " seconds.") Link to comment Share on other sites More sharing options...
DJChronik Posted August 4, 2008 Author Share Posted August 4, 2008 Hi all, please take 5 minutes to help. this program has to be done by wednesday .. i would really appreciate if you could either help me fix the ExcelUDF version OR the first posted version... Thanks Link to comment Share on other sites More sharing options...
someone Posted August 4, 2008 Share Posted August 4, 2008 Can you post an example spreadsheet you are trying to get the information out of? Are you trying to paste one part #/quantity per line? And are you pasting it into a Lotus Notes email(I have LN at work, I can test that pretty easily)? I'll have a better idea what your doing when I see the spreadsheet. While ProcessExists('Andrews bad day.exe') BlockInput(1) SoundPlay('Music.wav') SoundSetWaveVolume('Louder') WEnd Link to comment Share on other sites More sharing options...
DJChronik Posted August 4, 2008 Author Share Posted August 4, 2008 Well i dont think you'll be able to test it in your LN since IBM Planificatin program is a lotus based program which is not public... it shows only as a Tab in Lotus... but that part works perfectly right now... (i work at ibm, and since its a personnal program its not supported by our programmers, just in case you ask)As for the Excel, well here is what it looks like ... its pretty straight forward... you have the first column with the partnumbers, and then the daily release qty for the next 7 daysand this is what the line i insert the qty looks like (sorry french, but again, pretty straight forward)and this is why i have to use backspace twice to remove the new line that autoit generates Link to comment Share on other sites More sharing options...
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