Jump to content
Sign in to follow this  
DanielTyrkiel

[Solved] Stuck trying to copy an empty cell

Recommended Posts

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:

#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 by DanielTyrkiel

Share this post


Link to post
Share on other sites
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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 " ")?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by DanielTyrkiel

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  

×