Ram Posted October 18, 2007 Share Posted October 18, 2007 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! Link to comment Share on other sites More sharing options...
PsaltyDS Posted October 18, 2007 Share Posted October 18, 2007 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 Link to comment Share on other sites More sharing options...
Ram Posted October 19, 2007 Author Share Posted October 19, 2007 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? Link to comment Share on other sites More sharing options...
PsaltyDS Posted October 19, 2007 Share Posted October 19, 2007 (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 October 19, 2007 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now