_Excel_RangeCopyPaste

[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 May 26, 2015 by Happy82

[water]

Could you try:

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

 

[water]

I think you would need

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

because else you would overwrite the last line.

Edited May 26, 2015 by water

[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 May 26, 2015 by Happy82

[water]

This line is wrong:

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

Should be:

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

 

Edited May 26, 2015 by water

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

[water]

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

[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

 

[Happy82]

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

i have quick question on this variable

$xlCellTypeLastCell

what does t do?

[water]

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