Jump to content

_Excel_RangeCopyPaste not copying B:E range for each row


Recommended Posts

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:

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

Link to comment
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

:)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

×
×
  • Create New...