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 (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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 (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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 (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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 (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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 (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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 (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - 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

    • robertocm
      By robertocm
      I'm using this for replacing text strings in the VBProject of all excel files in a folder and subfolders.
      I have the same text string in several lines and those lines could have some differences between files: then not feasible for .ReplaceLine method
      I'm not interested in placing all the code in a xla AddIn, because the code is similar but not exactly the same in all the files.
       
      Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase #include <File.au3> #include <WinAPIFiles.au3> #include <Excel.au3> $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling. Global Const $sMessage = "Select Folder" Global $sFileSelectFolder = FileSelectFolder($sMessage, "") If @error Then MsgBox(0, "", "No folder was selected.") Exit EndIf Global $bFileOpen ;Look for excel files in selected directory and all subdirectories Global $aFileList = _FileListToArrayRec($sFileSelectFolder, "*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then Local $oAppl = _Excel_Open(Default, Default, False, Default, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended) For $i = 1 To $aFileList[0] $bFileOpen = _WinAPI_FileInUse($aFileList[$i]) If $bFileOpen = 0 Then ;ShellExecute($aFileList[$i]) Local $oWorkbook = _Excel_BookOpen($oAppl, $aFileList[$i]) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oProject = $oWorkbook.VBProject ;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html ;2.7.2 Macromodule delete With $oProject .VBComponents.Remove(.VBComponents("SplashText")) If $iEventError Then Consolewrite("SplashText Form not found: " & $aFileList[$i] & @CRLF) $iEventError = 0 ; Reset after displaying a COM Error occurred EndIf .VBComponents.Import("C:\Documents and Settings\XP\Escritorio\PLANTILLAS_EXPORT\SplashText.frm") EndWith ;3.2.1.8 Macro: delete With $oProject.VBComponents("Actual").CodeModule ;3.2.1.2 Macro: find If .Find("Sub Check_NumPed(", 1, 1, -1, -1) Then ;Note: using '+ 1' at the end of the line because i'm used to add an empty line between procedures (see vba help for ProcCountLines) .DeleteLines( .ProcStartLine("Check_NumPed", 0), .ProcCountLines("Check_NumPed", 0) + 1) EndIf EndWith ;Check if range name exists. If not create named ranges If Not IsObj($oWorkbook.Sheets("DATOS").Evaluate("Booking_DestPort")) Then ;If Not IsObj($oWorkbook.Sheets("DATOS").Range("Booking_DestPort")) Then If $oWorkbook.Sheets("DATOS").Range("AC7").value = "DestPort" Then $oWorkbook.Names.Add("Booking_DestPort", "=DATOS!$AC$8") Else ConsoleWrite("-> Not: 'DestPort' in AC7" & @TAB & $aFileList[$i] & @CRLF) EndIf If $oWorkbook.Sheets("DATOS").Range("AD7").value = "FinalDest" Then $oWorkbook.Names.Add("Booking_FinalDest", "=DATOS!$AD$8") Else ConsoleWrite("-> Not: 'FinalDest' in AD7" & @TAB & $aFileList[$i] & @CRLF) EndIf EndIf ;Open VBE Editor (like Alt+F11) $oAppl.VBE.MainWindow.Visible = True ;$oAppl.VBE.Windows("Inmediato").Visible = True ;https://www.autoitscript.com/forum/topic/77545-resolved-vbaofficeexcel-experts/ ;Spiff59, Aug 2008 ;Local $oModules = $oProject.VBComponents ;Local $oModules = $oWorkbook.VBProject.VBComponents ;$oModules.Item(1).CodeModule.CodePane.Show ;$oModules.Item(1).Activate ; With $oModules.Item($y).CodeModule ; .ReplaceLine (1 , "Sub SpellCheck()") ; .DeleteLines (10, 1) ; .InsertLines (7 , "TEST") ; EndWith ;Wait 30 seconds for the window to appear. Local $hWnd = WinWait("Microsoft Visual Basic - ", "Proyecto - VBAProjec", 30) WinActivate($hWnd) WinWaitActive($hWnd, "", 30) If WinActive($hWnd, "") Then ;Sleep(100) ;Send("{F7}") $oProject.VBComponents("Actual").Activate ;Wait 30 seconds for the window to appear. Local $hWnd2 = WinWait(" - [Actual (Código)]", "Proyecto - VBAProject", 30) WinActivate($hWnd2) WinWaitActive($hWnd2, "", 30) ;First Replace If WinActive($hWnd2, "") Then Send("{CTRLDOWN}h{CTRLUP}") ;Wait 30 seconds for the window to appear. Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30) WinActivate($hWnd3) WinWaitActive($hWnd3, "", 30) Sleep(100) Send('Sheets("DATOS").Range("AC8")') Sleep(200) Send("{TAB}") Sleep(100) ;Send("{DEL}") Send('Range("Booking_DestPort")') Sleep(200) ControlClick("Reemplazar", "", "[ID:4892]") Sleep(100) Send("{ALTDOWN}z{ALTUP}") Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2) ;WinActivate($hWnd4) ;WinWaitActive($hWnd4, "", 3) If WinActive($hWnd4, "") Then Sleep(100) Send("{SPACE}") Else Consolewrite("Not found 1: " & $aFileList[$i] & @CRLF) Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0) ;WinActivate($hWnd5) ;WinWaitActive($hWnd5, "", 2) Sleep(100) Send("{SPACE}") EndIf Sleep(100) If WinActive($hWnd3, "") Then ;Alt+F4 Send("!{F4}") Sleep(100) EndIf EndIf ;Second Replace If WinActive($hWnd2, "") Then Send("{CTRLDOWN}h{CTRLUP}") ;Wait 30 seconds for the window to appear. Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30) WinActivate($hWnd3) WinWaitActive($hWnd3, "", 30) Sleep(100) Send('Sheets("DATOS").Range("AD8")') Sleep(200) Send("{TAB}") Sleep(100) Send('Range("Booking_FinalDest")') Sleep(200) ControlClick("Reemplazar", "", "[ID:4892]") Sleep(100) Send("{ALTDOWN}z{ALTUP}") Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2) ;WinActivate($hWnd4) ;WinWaitActive($hWnd4, "", 3) If WinActive($hWnd4, "") Then Sleep(100) Send("{SPACE}") Else Consolewrite("Not found 2: " & $aFileList[$i] & @CRLF) Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0) ;WinActivate($hWnd5) ;WinWaitActive($hWnd5, "", 2) Sleep(100) Send("{SPACE}") EndIf Sleep(100) If WinActive($hWnd3, "") Then ;Alt+F4 Send("!{F4}") Sleep(100) EndIf EndIf ;Close VBE Editor If WinActive($hWnd2, "") Then $oAppl.VBE.ActiveWindow.Close ;Send("^{F4}") ;Sleep(100) ;Send("^s") $oAppl.VBE.MainWindow.Visible = False ;Sleep(100) ;Send("!{F4}") EndIf EndIf _Excel_BookClose($oWorkbook, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next Else MsgBox(16, "Error", "No files were found in the folder specified.") EndIf _Excel_Close($oAppl) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;This is a custom error handler Func ErrFunc() $HexNumber = Hex($oMyError.number, 8) ;~ MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _ ;~ "Number is: " & $HexNumber & @CRLF & _ ;~ "WinDescription is: " & $oMyError.windescription) ConsoleWrite("-> We intercepted a COM Error !" & @CRLF & _ "-> err.number is: " & @TAB & $HexNumber & @CRLF & _ "-> err.source: " & @TAB & $oMyError.source & @CRLF & _ "-> err.windescription: " & @TAB & $oMyError.windescription & _ "-> err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF) $iEventError = 1 ; Use to check when a COM Error occurs EndFunc ;==>ErrFunc  
    • gahhon
      By gahhon
      Hi Guys,
      I was trying to read some data from the excel file and without opening the file. But I tried a lot of methods, it still open the file.
      And also, I am able to capture the ColumnA value but not Column B.
      Thanks for advance information.
      Global $oDataA, $oDataB Call ("ExcelRead", "B2", "C2") Func ExcelRead($oColumnA, $oColumnB) Local $oPath = @ScriptDir & "\MyFile.xlsx" Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, $oPath, 1, 0) $oDataA = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnA) $oDataB = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnB) MsgBox(0, "Test Value", $oDataA & ", " & $oDataB) EndFunc  
    • ed973
      By ed973
      I really don't understand how to save as an open excel sheet.
      I run a script that at the end open the excel: just only need to save as the opened excel on my desktop (and overwrite it everytime).
      I'm trying to use .ActiveWorkBook.SaveAs("C:\Users\Enrico\Desktop\impegnato.xlsx") but...
       
      Thanks in advance for helping.
    • yasha
      By yasha
      i want am trying to select a nimber to run a program and then select where to save the excel result at before hand
      the problem is that it does not save in the folder i want but the folder before any solutions
      #.................
      $sFolder = ""
          ; Create a constant variable in Local scope of the message to display in FileSelectFolder.
          Local Const $sMessage = "Select a folder"
          ; Display an open dialog to select a file.
          $sFileSelectFolder = FileSelectFolder($sMessage, $sFolder)
          If @error Then
              ; Display the error message.
              MsgBox($MB_SYSTEMMODAL, "", "No folder was selected.")
          Else
              ; Display the selected folder.
              MsgBox($MB_SYSTEMMODAL, "", "You chose the following folder:" & @CRLF & $sFileSelectFolder)
          EndIf
      .......
      ........
      $oExcel = ObjCreate("Excel.Application")                   ; Create an Excel Object
      $oExcel.Visible = 1                                        ; Let Excel show itself
      $oExcel.Workbooks.Open("J:\OPS\OPS_Share\Planners\2 - Weekly Reports\Auto download\"& $YY & $MM & $DD & " ORDER.xls",0)
      $oExcel.ActiveWorkbook.Saveas ( $sFileSelectFolder,""& $YY & $MM & $DD & " ORDER.xlsx", 1)
      $oExcel.ActiveWorkBook.Close
      $oExcel.Quit
      i only want to save it as ddmmyy order inside documents but it saves in libraries as documents ddmmyy order.
    • PiyushJhawar
      By PiyushJhawar
      I am part of QA team of an analytics application. We support third party tools like Excel , Tableue .
      I have to write automation script that connect Excel to our analytics application. In short i want below to automate
      > Open Excel
      > Click on "Data" option available in header and then click on "From Other Services " then click on "From Analysis Services"
      > It will open pop up and then need to write username password there.
      I am new in this tool . Can any one please provide me link of any document that help me to create above script
×