Jump to content
jerem488

Copy row between excel files

Recommended Posts

jerem488
Posted (edited)

Hi,

I have 2 workbooks and I want copy some lines but I think I'm not using the function correctly ;)

My problem is that the lines between the two files don't copy each other!

I have many line _Excel_RangeCopyPaste(), it's for my tests !

#include <Excel.au3>

Func TransposeExcel()
    Local $oExcel = _Excel_Open(False, False, False, False, True)
    Local $oExcel_2 = _Excel_Open(False, False, False, False, True)

    Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Workbook1.xls")
    Local $oWorkbookExport = _Excel_BookOpen($oExcel_2, @DesktopDir & "\Workbook_2.xls")

    Local $sResultA1 = _Excel_RangeRead($oWorkbook, Default, "A1")
    If Not @error Then
        Local $nLine = 2
        Local $n = 2
        Do

            Local $sColumA = _Excel_RangeRead($oWorkbook, Default, "A" & $n)
            If $sColumA = "OC" Then

                _Excel_RangeCopyPaste($oWorkbook.Worksheets(1), '"' & $n & ":" & $n & '"')
                _Excel_RangeCopyPaste($oWorkbook, '"' & $n & ":" & $n & '"', $oWorkbookExport.Worksheets(1).Range.Rows($nLine), Default, $xlPasteFormats)

                _Excel_RangeCopyPaste($oWorkbookExport.Worksheets(1), Default, '"' & $nLine & ":" & $nLine & '"', Default, $xlPasteFormats)

;~              _Excel_RangeCopyPaste($oWorkbook, $n, $nLine);, Default, $xlPasteFormats)
                $nLine += 1
            EndIf
            $n += 1
        Until $sColumA = ""
        _Excel_BookClose($oWorkbookExport)
        _Excel_BookClose($oWorkbook)
        _Excel_Close($oExcel)
        _Excel_Close($oExcel_2)
    EndIf
EndFunc

 

Edited by jerem488

Qui ose gagneWho Dares Win[left]CyberExploit[/left]

Share this post


Link to post
Share on other sites
water

Example 2 in the help file for _Excel_RangeCopyPaste shows how to copy cells from one workbook to another one.

BTW: You can delete $oExcel_2 (and fully replace it with $oExcel) as there is only a single reference to the Excel application needed.


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
jerem488

I tested the exemple 2, but in the exemple he don't copy a row


Qui ose gagneWho Dares Win[left]CyberExploit[/left]

Share this post


Link to post
Share on other sites
water

I just ran example 2 and it worked fine.
Can you please post the modification you made to copy an entire row?


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
jerem488
Posted (edited)
_Excel_RangeCopyPaste($oWorkbook.Worksheets(1), $oWorkbook.Worksheets(1).Range.Rows($n), $oWorkbookExport.Worksheets(1).Range.Rows($nLine), False, $xlPasteValuesAndNumberFormats)

 

Edited by jerem488

Qui ose gagneWho Dares Win[left]CyberExploit[/left]

Share this post


Link to post
Share on other sites
water

You have to specify on which range you intend to work. Example:

#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkbook, 1, "Test", "A1:G1")
_Excel_RangeCopypaste($oWorkbook.Sheets(1), $oWorkbook.Sheets(1).Range("A1").entirerow, $oWorkbook.Sheets(1).Range("A2"))

 

  • Thanks 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
jerem488
23 minutes ago, water said:

You have to specify on which range you intend to work. Example:

#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkbook, 1, "Test", "A1:G1")
_Excel_RangeCopypaste($oWorkbook.Sheets(1), $oWorkbook.Sheets(1).Range("A1").entirerow, $oWorkbook.Sheets(1).Range("A2"))

 

You are genious !

Thanks a lot !

 


Qui ose gagneWho Dares Win[left]CyberExploit[/left]

Share this post


Link to post
Share on other sites
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

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

×