Sign in to follow this  
Followers 0
Ram

ExcelCOM_UDF.au3

4 posts in this topic

Hi,

I am looking for a way to copy the whole sheet and paste it in a different sheet. I have written scripts where the row and column is specified the excel cell will be copied and pasted accordingly but I am not able to find a solution to copy the whole sheet and paste in another excel file.

if sheet cannot be copied and can somebody atleast tell me the range of copy A1 to end range?

Awaiting your response!

Thanks!

Share this post


Link to post
Share on other sites



1. _ExcelSheetUsedRangeGet($oExcel_1, $vSheet)

2. Use the values from that to do _ExcelCopy($oExcel_1, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd) to the clipboard

3. _ExcelPaste($oExcel_2, $iRangeOrRow, $iColumn)

<_<


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

1. _ExcelSheetUsedRangeGet($oExcel_1, $vSheet)

2. Use the values from that to do _ExcelCopy($oExcel_1, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd) to the clipboard

3. _ExcelPaste($oExcel_2, $iRangeOrRow, $iColumn)

<_<

Thanks a lot this is what I tried.... but I didn't get anything.. I created an excel file till N3.

#include <ExcelCOM_UDF.au3>
$sPath = @ScriptDir & "\Test.xls"

$oExcel = _ExcelBookOpen($sPath)
$vSheet = 'Test'

$oExcel1= _ExcelSheetUsedRangeGet($oExcel, $vSheet)

MsgBox (0, "", $oExcel1[0]);= I get value N3
MsgBox (0, "", $oExcel1[1]);= I get value R3C14
MsgBox (0, "", $oExcel1[2]);= I get 14

$oExcel2=_ExcelCopy($oExcel1, "A1", 1, 3, $oExcel1[2]); I hard cord row to 3 and column array value. and also hardcoded the column value too no difference.

$oExcel3 = _ExcelBookNew(1)

_ExcelPaste($oExcel2, "A1", $oExcel1[2])

but nothing happened....I can just see new excel file opened, Can you tell me what did I do wrong?

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Thanks a lot this is what I tried.... but I didn't get anything.. I created an excel file till N3.

but nothing happened....I can just see new excel file opened, Can you tell me what did I do wrong?

I think you have to provide the cell range one of two ways: either as a single range string (i.e. "A1:N3"), or by giving separate integers for each of the four coordinates. Can't test at the moment (OpenOffice doesn't expose the same COM interface), but try this:

#include <array.au3>
#include <ExcelCOM_UDF.au3>

$sPath = @ScriptDir & "\Test.xls"      ; has data to N3
$vSheet = 'Sheet1' 

$oExcel_1 = _ExcelBookOpen ($sPath)
If IsObj($oExcel_1) Then
    $avRange = _ExcelSheetUsedRangeGet ($oExcel_1, $vSheet)
    If IsArray($avRange) Then
        _ArrayDisplay($avRange, "Debug: $avRange"); [0] = N3, [1] = R3C14, [2] = 14

        ; An Excel range looks like "A1:N3"
        ;   Alternative range with integers:  _ExcelCopy($oExcel_1, 1, 1, $avRange[2], $avRange[3])
        If _ExcelCopy ($oExcel_1, "A1:" & $avRange[0]) Then
            $oExcel_2 = _ExcelBookNew (1)
            If Not _ExcelPaste ($oExcel_2, "A1:" & $avRange[0]) Then
                MsgBox(16, "Error", "_ExcelPaste() failed." & @CRLF & _
                        "@error = " & @error & "  @extended = " & @extended)
            EndIf
        Else
            MsgBox(16, "Error", "_ExcelCopy() failed." & @CRLF & _
                    "@error = " & @error & "  @extended = " & @extended)
        EndIf
    Else
        MsgBox(16, "Error", "_ExcelSheetUsedRangeGet() failed.")
    EndIf
Else
    MsgBox(16, "Error", "_ExcelBookOpen() failed.")
EndIf

<_<

Edit: Got to a box with MS Excel on it and tested - fixed some syntax errors and added some error checking. The above code (after tweaking) worked for me.

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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
Sign in to follow this  
Followers 0