[Solved] Stuck trying to copy an empty cell

[DanielTyrkiel]

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:

expandcollapse popup

#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

Edited October 2, 2012 by DanielTyrkiel

[water]

_ExcelBookAttach is not necessary after _ExcelBookOpen. Use:

Local $oExcel = _ExcelBookOpen($sFilePath)

Don't use MouseClick and Win* functions to access the content of a web site. Use the IE UDF - gives more reliable applications.

[DanielTyrkiel]

_ExcelBookAttach is not necessary after _ExcelBookOpen. Use:

Local $oExcel = _ExcelBookOpen($sFilePath)

Don't use MouseClick and Win* functions to access the content of a web site. Use the IE UDF - gives more reliable applications.

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

[water]

So you want to automate a Java application running in the IE browser?

Java applications are hard to automate because they don't use windows controls.

There is a available written by seangriffin. Maybe this helps.

[DanielTyrkiel]

So you want to automate a Java application running in the IE browser?

Java applications are hard to automate because they don't use windows controls.

There is a available written by seangriffin. Maybe this helps.

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

[water]

If there is nothing to copy (the variable contains "") would it be OK for the target application to receive a single space (the variable contains " ")?

[DanielTyrkiel]

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

Edited October 2, 2012 by DanielTyrkiel