ExcelCOM_UDF.au3

[Ram]

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!

[PsaltyDS]

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)

[Ram]

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?

[PsaltyDS]

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 October 19, 2007 by PsaltyDS