Jump to content

For loop stops after first row in Excel


Recommended Posts

I have a script that opens up excel, copies a link from column A and sends it to the browser and hits Enter for the new browser to populate in the URL address. Then in column B it sends data to an input box. The script works on the first row for column A and column B, but the loop stops after this. Not sure why its stopping after that. What I'm wanting to do is continue to loop until it reaches the end. Any help is appreciated. Below is my script:

#Include "wd_core.au3"
#Include "wd_helper.au3"
#Include "wd_core.au3"
#Include "File.au3"
#Include "Array.au3"
#Include "Excel.au3"

Local $sDesiredCapabilities, $sSession
_WD_Startup()

$Ssession = _WD_CreateSession($sDesiredCapabilities)

_WD_Navigate($sSession, "https://127.0.0.1/test.html?id=1")

_WD_LoadWait($sSession)

Local $oExcel = _Excel_Open()

Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\<Username>\Downloads\test.xlsx")

Sleep(5000)

For $i = 0 To UBound($aArray1, $aArray2) - 1 
    _Excel_RangeCopyPaste($oWorkbook, $aArray1[$i])
    
    WinActivate("[Class:Chrome]")
    
    send ("!d")
    
    Sleep(5000)
    
    Send(aArray1[$i])
    
    Sleep(5000)
    
    Send("{ENTER}")
    
    Send(aArray1[$i])
    
    Sleep(5000)
    
    Send("{ENTER}")
    
    $test = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='test']")
    _WD_SetElementValue($sSession, $test, $aArray2[$i])
Next

_WD_Shutdown()

Func SetupChrome()
    _WD_Option('Driver', 'chromedriver.exe')
    _WD_Option('Port', 9515)
    _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"')
    $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}'
EndFunc   ;==>SetupChrome

HTML:

<!DOCTYPE html>
<html>
<body>

<h2>TEST</h2>

  <label for="test">Test</label><br>
  <input type="text" id="test" name="test" value="">

</body>
</html>

 

test.xlsx

Edited by goku200
Link to post
Share on other sites

Your ubound is wrongly formatted.  See help file...

You should consider using _WD_Navigate instead of send keys.

Link to post
Share on other sites

I updated my code to navigate instead of the send key. Still having the same issue:

#Include "wd_core.au3"
#Include "wd_helper.au3"
#Include "wd_core.au3"
#Include "File.au3"
#Include "Array.au3"
#Include "Excel.au3"

Local $sDesiredCapabilities, $sSession
_WD_Startup()

$Ssession = _WD_CreateSession($sDesiredCapabilities)

_WD_Navigate($sSession, "https://127.0.0.1/test.html?id=1")

_WD_LoadWait($sSession)

Local $oExcel = _Excel_Open()

Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\<Username>\Downloads\test.xlsx")

Sleep(5000)

For $i = 0 To UBound($aArray) - 1 
    _Excel_RangeCopyPaste($oWorkbook, $aArray1[$i])
    
    WinActivate("[Class:Chrome]")
    
    send ("!d")
    
    Sleep(5000)
 
    _WD_Navigate($sSession, $aArray[$i])
    
    $test = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='test']")
    _WD_SetElementValue($sSession, $test, $aArray2[$i])
Next

_WD_Shutdown()

Func SetupChrome()
    _WD_Option('Driver', 'chromedriver.exe')
    _WD_Option('Port', 9515)
    _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"')
    $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}'
EndFunc   ;==>SetupChrome



 

Edited by goku200
Link to post
Share on other sites

Oops! I went ahead and added SetupChrome()  at the top:

#Include "wd_core.au3"
#Include "wd_helper.au3"
#Include "wd_core.au3"
#Include "File.au3"
#Include "Array.au3"
#Include "Excel.au3"

Local $sDesiredCapabilities, $sSession

SetupChrome()

_WD_Startup()

$Ssession = _WD_CreateSession($sDesiredCapabilities)

_WD_Navigate($sSession, "https://127.0.0.1/test.html?id=1")

_WD_LoadWait($sSession)

Local $oExcel = _Excel_Open()

Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\<Username>\Downloads\test.xlsx")

Sleep(5000)

For $i = 0 To UBound($aArray) - 1 
    _Excel_RangeCopyPaste($oWorkbook, $aArray[$i])
    
    WinActivate("[Class:Chrome]")
    
    send ("!d")
    
    Sleep(5000)
 
    _WD_Navigate($sSession, $aArray1[$i])
    
    $test = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='test']")
    _WD_SetElementValue($sSession, $test, $aArray2[$i])
Next

_WD_Shutdown()

Func SetupChrome()
    _WD_Option('Driver', 'chromedriver.exe')
    _WD_Option('Port', 9515)
    _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"')
    $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}'
EndFunc   ;==>SetupChrome

 

Edited by goku200
Link to post
Share on other sites
4 minutes ago, Danp2 said:

What are these lines supposed to be doing? Seems like they aren't needed --

_Excel_RangeCopyPaste($oWorkbook, $aArray1[$i])
    WinActivate("[Class:Chrome]")
    send ("!d")
    Sleep(5000)

Have you checked the contents of $aArray1 / $aArray2?

When I run the script on my end it copies the array of the first cell in the spreadsheet and paste the value of it in the web address url. The array $aArray2 is the second column the value is set:

$test = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='test']")
_WD_SetElementValue($sSession, $test, $aArray2[$i])

test.xlsx

Edited by goku200
Link to post
Share on other sites

@j1osu2002 I understand what you are wanting the script to do. Like I mentioned, I don't believe the section of code I quotes is required now that you are using the Webdriver functions instead of trying to paste into the browser.

FWIW, this runs as I would expect --

#Include "wd_core.au3"
#Include "wd_helper.au3"
#Include "wd_core.au3"
#Include "File.au3"
#Include "Array.au3"
#Include "Excel.au3"

Local $sDesiredCapabilities, $sSession

SetupChrome()
_WD_Startup()

$Ssession = _WD_CreateSession($sDesiredCapabilities)

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
Local $aArray1 = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
Local $aArray2 = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Usedrange.Columns("B:B"))
_Excel_Close($oExcel)

For $i = 0 To UBound($aArray1) - 1
    _WD_Navigate($sSession, $aArray1[$i])

    $test = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='test']")
    _WD_SetElementValue($sSession, $test, $aArray2[$i])
Next

_WD_Shutdown()

Func SetupChrome()
    _WD_Option('Driver', 'chromedriver.exe')
    _WD_Option('Port', 9515)
    _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"')
    $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}'
EndFunc   ;==>SetupChrome

Of course, can't find / set the element value since there isn't a server to render those pages.

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...