Jump to content
Happy82

_Excel_RangeCopyPaste

Recommended Posts

Happy82

 

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Debug.au3>
_DebugSetup()
_DebugCOMError()

Local $oAppl = _Excel_Open()
    If @error Then Exit ConsoleWrite("Error creating application object" & @CRLF)

Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Unassigned Credit Card Transactions (MAIN REPORT).xlsx")
    If @error Then Exit ConsoleWrite("Error creating workbook object" & @CRLF)


Local $aWorkSheets = _Excel_SheetList($oWorkbook)
    If @error Then Exit ConsoleWrite("Error enumerating worksheets" & @CRLF)

For $i = 0 To UBound($aWorkSheets) - 1
    Local $iRowTarget = $oWorkbook.ActiveSheet(1).UsedRange.Rows.Count
    Local $iRowno = $oWorkbook.ActiveSheet.UsedRange.Rows.Count    
    Local $iRownos = "6:" & $iRowno           
   _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $iRownos, $iRowTarget)
   If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: Copy1", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   MsgBox($MB_SYSTEMMODAL, "Excel UDF: Excel UDF: Copy", "Successfully pasted")
Next
Exit

 

I am trying to copy the contents from the current page (i.e.., page 2) and paste it the 1st sheet of the workbook. In the 1st worksheet i have to paste it below the already existing contents. So i am trying to find the last row in the worksheet 1 and use it as target range object. But it throws an error 3 (invalid target range).

Any help is highly appreciated.

 

Edited by Happy82

Share this post


Link to post
Share on other sites
water

Could you try:

_Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $iRownos, "A" & $iRowTarget)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - 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
water

I think you would need

_Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $iRownos, "A" & ($iRowTarget + 1))

because else you would overwrite the last line.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - 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
Happy82

It throws the same error, but the problem i see here is value is empty for the following line. I am not sure if the way i find the no of used rows from the worksheet 1 is incorrect?

Local $iRowTarget = $oWorkbook.ActiveSheet(1).UsedRange.Rows.Count

I am pasting the actual code

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Debug.au3>
_DebugSetup()
_DebugCOMError()

Local $oAppl = _Excel_Open()
    If @error Then Exit ConsoleWrite("Error creating application object" & @CRLF)

Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Unassigned Credit Card Transactions (MAIN REPORT).xlsx")
    If @error Then Exit ConsoleWrite("Error creating workbook object" & @CRLF)


Local $aWorkSheets = _Excel_SheetList($oWorkbook)
    If @error Then Exit ConsoleWrite("Error enumerating worksheets" & @CRLF)

For $i = 0 To UBound($aWorkSheets) - 1
    MsgBox(64,"Res", "The Name of worksheet :" & $aWorkSheets[$i][0])
    Local $vSheet = $aWorkSheets[$i][0]
    $oWorkbook.Sheets($vSheet).Select
    Local $iRowTarget = $oWorkbook.ActiveSheet(1).UsedRange.Rows.Count
    Local $iRowno = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
    MsgBox(64,"Row No Source", "No of Rows in the Source : " & $iRowno)
    MsgBox(64,"Row No Target", "No of Rows in the Target : " & $iRowTarget)
    Local $iRownos = "6:" & $iRowno
    If ($i > 0) Then
      _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $iRownos, "A" & $iRowTarget)
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: Copy1", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      MsgBox($MB_SYSTEMMODAL, "Excel UDF: Excel UDF: Copy", "Successfully pasted")
   EndIf
Next
Exit

 

Edited by Happy82

Share this post


Link to post
Share on other sites
water

This line is wrong:

Local $iRowTarget = $oWorkbook.ActiveSheet(1).UsedRange.Rows.Count

Should be:

Local $iRowTarget = $oWorkbook.ActiveSheet.UsedRange.Rows.Count

 

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - 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
Happy82

But if i do so, it will copy the contents to the same worksheet.

So i was trying to get the no of rows in the 1 worksheet  in the following line.

Local $iRowTarget = $oWorkbook.ActiveSheet(1).UsedRange.Rows.Count

Probably i was not clear in explaining....

I have a workbook which has multiple worksheets. I will read through the each worksheet, if the reading sheet is not the 1st worksheet then i will copy the contents of the current worksheet and paste it the last last + 1 used row of the worksheet 1.

I workbook i am using has 3 worksheets, while reading the 1st work sheet i don't want to copy/paste the contents... while reading the 2 worksheet i will have to read the contents of the worksheet and copy it to the worksheet 1 at the last +1 used row. I want  to do the same thing while reading the 3 worksheet.

Share this post


Link to post
Share on other sites
water

I see.
Will post an example as soon as I return to my office :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - 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
water

This should work:

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox(0, "", "Error creating the Excel application object.")
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Test.xlsx")
If @error Then
    MsgBox(0, "", "Error opening workbook")
    _Excel_Close($oAppl)
    Exit
EndIf

$iSheets = $oWorkbook.Sheets.Count ; Get number of worksheets
If $iSheets > 1 Then
    For $i = 2 To $iSheets
        $iTargetRow = $oWorkbook.Sheets(1).UsedRange.SpecialCells($xlCellTypeLastCell).Row + 1 ; Get the number of the first unused row
        _Excel_RangeCopyPaste($oWorkbook.Sheets($i), $oWorkbook.Sheets($i).UsedRange.Entirerow, $oWorkbook.Sheets(1).Range("A" & $iTargetRow))
        ConsoleWrite(@error & @CRLF)
    Next
EndIf

 

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - 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
Happy82

It works perfect..... thanks....... water....

i have quick question on this variable

$xlCellTypeLastCell

what does t do?

Share this post


Link to post
Share on other sites
water

It gives you the address of the lower right cell of a range.
It is described in the wiki.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - 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

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

  • Similar Content

    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework: (edited)
      #include <MsgBoxConstants.au3> #include <Array.au3> #include <Excel.au3> HotKeySet("!v", "Pastedata") While True Sleep(1000) WEnd func Makearray() local $bArray ;User has cells already copied ;Convert clipboard into an array ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array _Arraydisplay($bArray) MsgBox(0,0,$bArray) return $bArray endfunc func Pastedata() Local $aArray MsgBox(0,0,"wait",1) ;make array based on assumption user has already copied a range to clipboard $aArray = Makearray() ;paste code ;don;t worry about this I got the rest endfunc  
×