RPAChallenge with AHK, COM, and JSWrapper.ahk

Post your working scripts, libraries and tools for AHK v1.1 and older
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

RPAChallenge with AHK, COM, and JSWrapper.ahk

05 Jul 2020, 16:58

I found @Fade's wrapper buried in the 'Ask for help' posts. RPA training often involves doing the challenge at http://rpachallenge.com.

This wrapper can pass variables back and forth between AHK and JS. See the example at the link above.

Here's the code for the challenge (quick and dirty, sorry about that).

Code: Select all

#Include JSWrapper.ahk
; THANKS to Fade who got almost no response to this great JS wrapper that I can see.
; Get the wrapper here:
; https://www.autohotkey.com/boards/viewtopic.php?f=76&t=47716&p=213972
; (April 24, 2018 post)
; burque505, July 5, 2020


OnExit("Cleanup")

; Get the challenge file
URLDownloadToFile, http://rpachallenge.com/assets/downloadFiles/challenge.xlsx, challenge.xlsx

; Create arrays for each Excel column
firstNameArray := []
lastNameArray := []
companyNameArray := []
roleArray := []
addressArray := []
emailArray := []
phoneArray := []

lastrow := 0 ; Used later
path := A_ScriptDir
xl := ComObjCreate("Excel.Application")
xl.Visible := true

xl.Workbooks.Open(path . "\challenge.xlsx")

;loop to fill the arrays
while (Xl.Range("A" . A_Index).Value != "") {
indexer := A_Index -1
firstNameArray[indexer] := Xl.Range("A" . A_Index).Value
lastNameArray[indexer] := Xl.Range("B" . A_Index).Value
companyNameArray[indexer] := Xl.Range("C" . A_Index).Value
roleArray[indexer] := Xl.Range("D" . A_Index).Value
addressArray[indexer] := Xl.Range("E" . A_Index).Value
emailArray[indexer] := Xl.Range("F" . A_Index).Value
phoneArray[indexer] := Xl.Range("G" . A_Index).Value
lastrow := A_Index -1
}

; Don't need Excel anymore.
xl.Quit

; Start of of JSWrapper example code. See the link above for more information.
hmOleAut32 := DllCall("LoadLibrary", "Str", "OleAut32.dll", "Ptr")
ie := ComObjCreate("InternetExplorer.Application")

; Go to challenge website
ie.Navigate("http://rpachallenge.com")
ie.Visible := true
while (ie.ReadyState != 4)
	Sleep 10

window := new JSWrapper(ie.document.parentWindow)

jsStart =
(
		window.moveTo(0, 0);
		window.resizeTo(screen.availWidth, screen.availHeight);
		start = document.querySelector('button.waves-effect');
		start.click(); 
)

; Click the 'Start' Button
window.eval(jsStart)

; Do the challenge
loop, %lastrow% { ; start loop
fnvar = % firstNameArray[A_Index]
lnvar = % lastNameArray[A_Index]
cnvar = % companyNameArray[A_Index]
rolevar = % roleArray[A_Index]
addressvar = % addressArray[A_Index]
emailvar = % emailArray[A_Index]
phonevar = % phoneArray[A_Index]
window["firstName"] := fnvar
window["lastName"] := lnvar
window["companyName"] := cnvar
window["role"] := rolevar
window["address"] := addressvar
window["email"] := emailvar
window["phone"] := phonevar
; The variables change value each time through the loop
js =
(
		m = document.querySelector('input[ng-reflect-name="labelEmail"]');
		m.value = email;
		a = document.querySelector('input[ng-reflect-name="labelAddress"]');
		a.value = address;
		f = document.querySelector('input[ng-reflect-name="labelFirstName"]');
		f.value = firstName;
		l = document.querySelector('input[ng-reflect-name="labelLastName"]');
		l.value = lastName;
		p = document.querySelector('input[ng-reflect-name="labelPhone"]');
		p.value = phone;
		r = document.querySelector('input[ng-reflect-name="labelRole"]');
		r.value = role;
		c = document.querySelector('input[ng-reflect-name="labelCompanyName"]');
		c.value = companyName;
		s = document.querySelector('input[value="Submit"]');
		s.click();
)

window.eval(js)

} ;end loop

DllCall("FreeLibrary", "Ptr", hmOleAut32)

Escape::
if (WinExist("ahk_exe iexplore.exe"))
	ie.Quit()
	DllCall("FreeLibrary", "Ptr", hmOleAut32)
ExitApp

; You can figure out a better exit strategy, I'm sure.

Cleanup() {
	global
	if (WinExist("ahk_exe iexplore.exe"))
		ie.Quit()
	DllCall("FreeLibrary", "Ptr", hmOleAut32)
}
My early results (but best so for is 790 ms on Win7 and 630ms on Win10. Earlier results of over 1100 ms were deleted.): :D
Success.PNG
Success.PNG (12.73 KiB) Viewed 8258 times
Regards,
burque505
blue83
Posts: 157
Joined: 11 Apr 2018, 06:38

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

06 Jul 2020, 13:11

Hi @burque505 ,

Nice, but you know my best result is 310ms using only AHK :)

blue
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

06 Jul 2020, 13:37

:bravo: Love to see it! That's fast.
Regards,
burque505
blue83
Posts: 157
Joined: 11 Apr 2018, 06:38

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

06 Jul 2020, 14:18

Thanks, yes it require some another approach than usual loop and I did it.

And there is noone from UIpath, Blue Prism or any another tool that is faster than that result.

There are some tricks with JS, but i did not see a video of that.

Here is a picture.
Capture.PNG
Capture.PNG (71.49 KiB) Viewed 7211 times
AHKStudent
Posts: 1472
Joined: 05 May 2018, 12:23

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

06 Jul 2020, 21:12

would make a great video to demonstrate the ability of a bot
aifritz
Posts: 301
Joined: 29 Jul 2018, 11:30
Location: Germany

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 00:43

@blue83: This is amazingly fast :clap:
Do you want to share your code?
blue83
Posts: 157
Joined: 11 Apr 2018, 06:38

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 00:45

Still waiting if anyone will be better to learn something :)

But yes if there will be no one.
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 07:27

:-D @blue83, I'd like to see it too of course!
I'm going to have a try at converting this Python code by Repoman to AHK: driver = webdriver.Chrome()
driver.get("http://www.rpachallenge.com/"), I had to redo a few statements:

Code: Select all

from selenium import webdriver
from selenium.webdriver.common.keys import Keys
import openpyxl as openpyxl

driver = webdriver.Chrome()
driver.get("http://rpachallenge.com")
wb = openpyxl.load_workbook('challenge.xlsx')
ws = wb.active
col_names = [ws.cell(row=1,column=j).value.strip() for j in range(1,8)]
 
driver.execute_script("document.querySelector('button.waves-effect').click();")
 
for i in range(2,12):
    vals = [ws.cell(row=i,column=j).value for j in range(1,8)]
    scripts = ["document.evaluate('//label[contains(text()," + '"' + col_names[idx]  + '"' + ")]', document, null, XPathResult.FIRST_ORDERED_NODE_TYPE, null).singleNodeValue.parentElement.getElementsByTagName('input')[0].setAttribute('value', '" + str(val) + "')" for idx, val in enumerate(vals)]
    script_to_be_executed = ";".join(scripts)
    driver.execute_script(script_to_be_executed)
    driver.execute_script("document.querySelector(`input[value='Submit']`).click()")
That's not much code. Repoman reports 117 milliseconds! I got 363 milliseconds. Looks like that XPath expression is key.
python.PNG
python.PNG (12.02 KiB) Viewed 5509 times
Regards,
burque505
blue83
Posts: 157
Joined: 11 Apr 2018, 06:38

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 10:23

Ok, then there is a beter score then mine.

I am on holiday now, so dont have code with me, but thing is that after using loop and array and time around 1500ms I figured that I need do some manual work and take all the variables from excel before starting and only use "else if" and thats way I did it, nothing special but faster.

Blue
blue83
Posts: 157
Joined: 11 Apr 2018, 06:38

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 10:25

p.s. @burque505 ,

That is really improvement, well done :)
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 10:43

Thanks, now to convert to AHK . . .
Regards,
burque505
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 14:11

No XPath version yet, but SeleniumBasic works (via SChrome_Get.ahk, that is). This is Win7, ancient machine, should be faster on Win10 (haven't got SeleniumBasic working on a Win10 machine yet).

Code: Select all

; RPA Challenge with SeleniumBasic
; burque505, July 5, 2020

; Get the challenge file
URLDownloadToFile, http://rpachallenge.com/assets/downloadFiles/challenge.xlsx, challenge.xlsx

; Create arrays for each Excel column
firstNameArray := []
lastNameArray := []
companyNameArray := []
roleArray := []
addressArray := []
emailArray := []
phoneArray := []

lastrow := 0 ; Used later
path := A_ScriptDir
xl := ComObjCreate("Excel.Application")
xl.Visible := true

xl.Workbooks.Open(path . "\challenge.xlsx")

;loop to fill the arrays
while (Xl.Range("A" . A_Index).Value != "") {
indexer := A_Index -1
firstNameArray[indexer] := Xl.Range("A" . A_Index).Value
lastNameArray[indexer] := Xl.Range("B" . A_Index).Value
companyNameArray[indexer] := Xl.Range("C" . A_Index).Value
roleArray[indexer] := Xl.Range("D" . A_Index).Value
addressArray[indexer] := Xl.Range("E" . A_Index).Value
emailArray[indexer] := Xl.Range("F" . A_Index).Value
phoneArray[indexer] := Xl.Range("G" . A_Index).Value
lastrow := A_Index -1
}

; Don't need Excel anymore.
xl.Quit


#Include SChrome_Get.ahk
driver := SChrome_Get("http://www.rpachallenge.com")
driver.Window.Maximize
driver.ExecuteScript("document.querySelector('button.waves-effect').click();")

loop, %lastrow% { ; start loop
fnvar = % firstNameArray[A_Index]
lnvar = % lastNameArray[A_Index]
cnvar = % companyNameArray[A_Index]
rolevar = % roleArray[A_Index]
addressvar = % addressArray[A_Index]
emailvar = % emailArray[A_Index]
phonevar = % phoneArray[A_Index]
;Phone is the problem. It's a float.
;~ ; The variables change value each time through the loop

js = 
(
		m = document.querySelector('input[ng-reflect-name="labelEmail"]');
		m.value = "%emailvar%";
		a = document.querySelector('input[ng-reflect-name="labelAddress"]');
		a.value = "%addressvar%";
		f = document.querySelector('input[ng-reflect-name="labelFirstName"]');
		f.value = "%fnvar%";
		l = document.querySelector('input[ng-reflect-name="labelLastName"]');
		l.value = "%lnvar%";
		p = document.querySelector('input[ng-reflect-name="labelPhone"]');
		p.value = Math.trunc("%phonevar%");
		r = document.querySelector('input[ng-reflect-name="labelRole"]');
		r.value = "%rolevar%";
		c = document.querySelector('input[ng-reflect-name="labelCompanyName"]');
		c.value = "%cnvar%";
        s = document.querySelector('input[value="Submit"]');
		s.click();

)

driver.ExecuteScript(js)
} ;end loop

sleep 5000
driver.Quit()
success_selenium.PNG
success_selenium.PNG (12.12 KiB) Viewed 4633 times
Ahah! Win10:
success_selenium_win10.PNG
success_selenium_win10.PNG (26.35 KiB) Viewed 4509 times
Edit: With the fixes from @Tre4shunter below, this code
Spoiler
I got this on Win10 (still can't match @Tre4shunter's score, though!):
success_selenium_tre4shunter.PNG
success_selenium_tre4shunter.PNG (25.15 KiB) Viewed 2557 times
Last edited by burque505 on 07 Jul 2020, 20:41, edited 1 time in total.
Tre4shunter
Posts: 139
Joined: 26 Jan 2016, 16:05

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 16:31

This looked fun!

I just moved the JS call outside of the loop...I also used GeekDudes Chrome class for this. No selenium.
rpa.JPG
rpa.JPG (55.53 KiB) Viewed 4468 times
:D
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 16:46

@Tre4shunter, wow!!! :bravo: Would you mind posting your entire code? That's one for the history books!
EDIT: I took a stab at it with Chrome.ahk, but my times on Win7 are still above 550 ms.

Code: Select all

#Include Chrome.ahk

URLDownloadToFile, http://rpachallenge.com/assets/downloadFiles/challenge.xlsx, challenge.xlsx

; Create arrays for each Excel column
firstNameArray := []
lastNameArray := []
companyNameArray := []
roleArray := []
addressArray := []
emailArray := []
phoneArray := []

lastrow := 0 ; Used later
path := A_ScriptDir
xl := ComObjCreate("Excel.Application")
xl.Visible := true

xl.Workbooks.Open(path . "\challenge.xlsx")

;loop to fill the arrays
while (Xl.Range("A" . A_Index).Value != "") {
indexer := A_Index -1
firstNameArray[indexer] := Xl.Range("A" . A_Index).Value
lastNameArray[indexer] := Xl.Range("B" . A_Index).Value
companyNameArray[indexer] := Xl.Range("C" . A_Index).Value
roleArray[indexer] := Xl.Range("D" . A_Index).Value
addressArray[indexer] := Xl.Range("E" . A_Index).Value
emailArray[indexer] := Xl.Range("F" . A_Index).Value
phoneArray[indexer] := Xl.Range("G" . A_Index).Value
lastrow := A_Index -1
}

; Don't need Excel anymore.
xl.Quit

js = 
(
		document.querySelector('input[ng-reflect-name="labelEmail"]').value = "%emailvar%";
		document.querySelector('input[ng-reflect-name="labelAddress"]').value = "%addressvar%";
		document.querySelector('input[ng-reflect-name="labelFirstName"]').value = "%fnvar%";
		document.querySelector('input[ng-reflect-name="labelLastName"]').value = "%lnvar%";
		document.querySelector('input[ng-reflect-name="labelPhone"]').value = Math.trunc("%phonevar%");
		document.querySelector('input[ng-reflect-name="labelRole"]').value = "%rolevar%";
		document.querySelector('input[ng-reflect-name="labelCompanyName"]').value = "%cnvar%";
        document.querySelector('input[value="Submit"]').click();

)

; Create an instance of the Chrome class using
; the folder ChromeProfile to store the user profile
;FileCreateDir, ChromeProfile
ChromeInst := new Chrome("ChromeProfile")

; Connect to the newly opened tab and navigate to another website
; Note: If your first action is to navigate away, it may be just as
; effective to provide the target URL when instantiating the Chrome class
PageInstance := ChromeInst.GetPage()
PageInstance.Call("Page.navigate", {"url": "http://rpachallenge.com/"})
PageInstance.WaitForLoad()

; Execute some JavaScript
PageInstance.Evaluate("document.querySelector('button.waves-effect').click();")


loop, %lastrow% { ; start loop
fnvar = % firstNameArray[A_Index]
lnvar = % lastNameArray[A_Index]
cnvar = % companyNameArray[A_Index]
rolevar = % roleArray[A_Index]
addressvar = % addressArray[A_Index]
emailvar = % emailArray[A_Index]
phonevar = % phoneArray[A_Index]
;Phone is the problem. It's a float.
;~ ; The variables change value each time through the loop

PageInstance.Evaluate(js)
} ;end loop


sleep 3000

;~ ; Close the browser (note: this closes *all* pages/tabs)
;~ PageInstance.Call("Browser.close")
;~ PageInstance.Disconnect()

ExitApp
return
Regards,
burque505
Last edited by burque505 on 07 Jul 2020, 17:12, edited 1 time in total.
Tre4shunter
Posts: 139
Joined: 26 Jan 2016, 16:05

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 17:11

You pretty much had it...i just simply removed the 7 JS calls...and made it one long Javascript execution, which is where the real time savings comes in i believe!

Here's the relevant stuff:

Code: Select all

#Include Chrome\Chrome.ahk
if !FileExist(A_Temp "\ChromeProfile")
	FileCreateDir, % A_Temp "\ChromeProfile"
ChromeInst := new Chrome(A_Temp "\ChromeProfile", "http://www.rpachallenge.com")
PageInst := ChromeInst.GetPage()
PageInst.WaitForLoad()

msgbox, Begin

pageInst.Evaluate("document.querySelector('button.waves-effect').click();")

loop, % LastRow 
{
js .=    "document.querySelector('input[ng-reflect-name=""labelEmail""]').value = '" emailArray[A_Index] "';"
		. "document.querySelector('input[ng-reflect-name=""labelAddress""]').value = '" addressArray[A_Index] "';"
		. "document.querySelector('input[ng-reflect-name=""labelFirstName""]').value = '" firstNameArray[A_Index] "';"
		. "document.querySelector('input[ng-reflect-name=""labelLastName""]').value = '" lastNameArray[A_Index] "';"
		. "document.querySelector('input[ng-reflect-name=""labelPhone""]').value = Math.trunc(" phoneArray[A_Index] ");"
		. "document.querySelector('input[ng-reflect-name=""labelRole""]').value = '" roleArray[A_Index] "';"
		. "document.querySelector('input[ng-reflect-name=""labelCompanyName""]').value = '" companyNameArray[A_Index] "';"
		. "document.querySelector('input[value=""Submit""]').click();"
} ;end loop
pageInst.Evaluate(js)
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 17:23

Thanks a million, @Tre4shunter. That got me to 313ms on Win7, I'll try it on Win10 now.
Win10: :bravo:
Tre4shunter.PNG
Tre4shunter.PNG (25.6 KiB) Viewed 4029 times
Your code also helped me with formatting JS for AHK, thank you. I have been struggling with the quoting.

Regards,
burque505
Tre4shunter
Posts: 139
Joined: 26 Jan 2016, 16:05

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 17:40

Awesome - glad i could help! :)
burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: RPAChallenge with AHK, COM, and JSWrapper.ahk

07 Jul 2020, 18:07

This is just for reference, not for speed. Plain Vanilla AHK, 816ms. No artificial flavors, preservatives or sweeteners.

Code: Select all

URLDownloadToFile, http://rpachallenge.com/assets/downloadFiles/challenge.xlsx, challenge.xlsx

; Create arrays for each Excel column
firstNameArray := []
lastNameArray := []
companyNameArray := []
roleArray := []
addressArray := []
emailArray := []
phoneArray := []

lastrow := 0 ; Used later
path := A_ScriptDir
xl := ComObjCreate("Excel.Application")
xl.Visible := true

xl.Workbooks.Open(path . "\challenge.xlsx")

;loop to fill the arrays
while (Xl.Range("A" . A_Index).Value != "") {
indexer := A_Index -1
firstNameArray[indexer] := Xl.Range("A" . A_Index).Value
lastNameArray[indexer] := Xl.Range("B" . A_Index).Value
companyNameArray[indexer] := Xl.Range("C" . A_Index).Value
roleArray[indexer] := Xl.Range("D" . A_Index).Value
addressArray[indexer] := Xl.Range("E" . A_Index).Value
emailArray[indexer] := Xl.Range("F" . A_Index).Value
phoneArray[indexer] := Xl.Range("G" . A_Index).Value
lastrow := A_Index -1
}

; Don't need Excel anymore.
xl.Quit

ie := ComObjCreate("InternetExplorer.Application")
ie.Visible := true  ; This is known to work incorrectly on IE7.
ie.Navigate("http://rpachallenge.com")
while (ie.Busy)
	sleep, 100

doc := ie.Document

doc.querySelector("button.waves-effect").click()

loop, %lastrow% {
fnvar = % firstNameArray[A_Index]
lnvar = % lastNameArray[A_Index]
cnvar = % companyNameArray[A_Index]
rolevar = % roleArray[A_Index]
addressvar = % addressArray[A_Index]
emailvar = % emailArray[A_Index]
phonevar = % phoneArray[A_Index]
doc.querySelector("input[ng-reflect-name='labelEmail']").value := emailvar
doc.querySelector("input[ng-reflect-name='labelAddress']").value := addressvar
doc.querySelector("input[ng-reflect-name='labelFirstName']").value := fnvar
doc.querySelector("input[ng-reflect-name='labelLastName']").value := lnvar
doc.querySelector("input[ng-reflect-name='labelPhone']").value := phonevar
doc.querySelector("input[ng-reflect-name='labelRole']").value := rolevar
doc.querySelector("input[ng-reflect-name='labelCompanyName']").value := cnvar
doc.querySelector("input[value='Submit']").click()

} ; end loop


sleep, 5000
ie.quit()

Return to “Scripts and Functions (v1)”

Who is online

Users browsing this forum: No registered users and 133 guests