Sign in to follow this  
Followers 0
DJChronik

Problems with IF and ClipGet

8 posts in this topic

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

$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!

Share this post


Link to post
Share on other sites



Look at Excel UDF in latest AutoIt

_ExcelReadCell

_ExcelReadArray

_ExcelReadSheetToArray

I have 3.2.12.1 and i dont see those anywhere?!

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 days

Posted Image

and this is what the line i insert the qty looks like (sorry french, but again, pretty straight forward)

Posted Image

and this is why i have to use backspace twice to remove the new line that autoit generates

Posted Image

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0