Jump to content

_Excel_RangeCopyPaste


Recommended Posts

If the workbook has multiple sheets then i am trying to copy the rows  from other sheets and paste in sheet 1.

So in the process i am trying to first read the contents in clipboard.

Following command works fine,

#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 $oRange = $oWorkbook.ActiveSheet.Range("6:20")       
   _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oRange)
   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", "2 Rows successfully pasted from the clipboard.")
Next
Exit

 

but the range is dynamic so i trying to use

#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 $iRowno = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
    Local $oRange = $oWorkbook.ActiveSheet.Range("6:$iRowno")       
   _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oRange)
   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", "2 Rows successfully pasted from the clipboard.")
Next
Exit

 

 

Edited by Happy82
Link to comment
Share on other sites

#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 $iRowno = $oWorkbook.ActiveSheet.UsedRange.Rows.Count    
    Local $iRownos = "6:" & $iRowno           
   _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $iRownos)
   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", "2 Rows successfully pasted from the clipboard.")
Next
Exit

 

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