_Excel_RangeCopyPaste not copying B:E range for each row

[Hermes]

My _Excel_RangeCopyPaste is not working as intended. What I am trying to accomplish is copy the range B:E using _Excel_RangeCopyPaste in the first row and repeat the same for row 2 and so on.

;Skip from reading header columns
Local $Skipline = 0 ;0==> first line

Local $temprf
For $i = 0 To UBound($aArray2) - 1

    If $Skipline = $i Then ContinueLoop

    $temprf &= $aArray2[$i]

   _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange)
   Local $oTest = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "PasteButton") ;This button will paste values from the Clipboard once clicked
   _WD_ElementAction($sSession, $oTest, 'click'))
Next

Here's the full code:

expandcollapse popup

#Include <Chrome.au3>
#Include <wd_core.au3>
#Include <wd_helper.au3>
#Include <WinHttp.au3>
#include <MsgBoxConstants.au3>
#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>


Local $sDesiredCapabilities, $sSession
SetupChrome()

_WD_Startup()


$sSession = _WD_CreateSession($sDesiredCapabilities)

_WD_LoadWait($sSession)

_WD_Navigate($sSession, "test.html")

Local $oAppl = _Excel_Open()
Local $sWorkbook = "test.xlsx"

Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters
Local $aArray2 = _Excel_RangeRead($oWorkbook,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
Local $oRange = $oWorkbook.ActiveSheet.Range("B:E")

;Skip from reading header columns
Local $Skipline = 0 ;0==> first line

Local $temprf
For $i = 0 To UBound($aArray2) - 1

    If $Skipline = $i Then ContinueLoop

    $temprf &= $aArray2[$i]

   _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange)
   Local $oTest = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "PasteButton") ;This button will paste values from the Clipboard once clicked
   _WD_ElementAction($sSession, $oTest, 'click'))
Next

_WD_Shutdown()

Func SetupChrome()
    _WD_Option('Driver', 'chromedriver.exe')
    _WD_Option('Port', 9515)
    _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"')
    Return '{"capabilities":{"alwaysMatch":{"goog:chromeOptions":{"w3c":true,' & _
    '"excludeSwitches":["enable-automation"],"useAutomationExtension":false}}}}'
EndFunc   ;==>SetupChrome

For the first row I am trying to copy just B:E with the following info

Apple

Banana

Orange

Mango

and then repeat for row 2, row 3, etc. I've attached the spreadhseet.

test.xlsxI have also attached the excel file for reference.

[water]

You should check @error and @extended after each call of a _Excel* and _WD* function.

The range you specify in your loop for _Excel_RangeCopypaste copies the full columns B:E to the clipboard.
You need to specify the row to copy e.g. B1:E1 for columns B to E in row 1.

[Hermes]

@water Thanks for responding quickly!

I can specify the rows e.g:

Local $oRange = $oWorkbook.ActiveSheet.Range("B2:E2")

But I am using a for loop, so once it's done copying b2:e2, it will proceed to the next row which is b3:e3 and so on until there is no more data to read on.

[water]

Use the counter to determine the row to copy:

Local $oRange, $oTest
Local $iStartLine = 0 ; First index of $aArray2 to process

For $i = 0 To UBound($aArray2) - 1
    If $i < $iStartLine Then ContinueLoop
    $oRange = $oWorkbook.ActiveSheet.Range("B" & $i + 1 & ":E" & $i + 1)
    $temprf &= $aArray2[$i]
   _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange)
   $oTest = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "PasteButton") ;This button will paste values from the Clipboard once clicked
   _WD_ElementAction($sSession, $oTest, 'click'))
Next

In addition I have

• Moved the Local definition out of the loop
• Replaced $Skipline with $iStartLine because your code only skipped a single line, not all lines up to the specified line

[Hermes]

Thanks, that worked!

[water]