[RESOLVED] Copy Excel sheet

[Ahbadiane]

Hello,

What I am trying to do : Copy an Excel sheet from one workbook to another existing workbook

What I managed to : Copy an Excel workbook in the same

The attached code causes the error :

(11) : ==> The requested action with this object has failed.

If IsObj($oCopyTo) Then $oFic1.ActiveWorkbook.Sheets($oSheetFic1).Copy($oCopyTo)^ ERROR

If I replace line 10 : Global $oCopyTo = $oFic2.ActiveWorkbook.Sheets(1)

By (to copy in the same workbook) : Global $oCopyTo = $oFic1.ActiveWorkbook.Sheets(1)

the copy is error free

If you have the solution to my problem ...

Ahbadiane

#include <Excel.au3>
Global Const $sFic1 = "Test1.xls"
Global Const $oSheetFic1 = "Sheet1"
Global Const $sFic2 = "Test2.xls"

Global $oFic1 = _ExcelBookOpen(@ScriptDir & "\" & $sFic1)
Global $oFic2 = _ExcelBookOpen(@ScriptDir & "\" & $sFic2)

; Destination of the copy ($oFic1 = OK, $oFic2 = K0)
Global $oCopyTo = $oFic2.ActiveWorkbook.Sheets(1)
If IsObj($oCopyTo) Then $oFic1.ActiveWorkbook.Sheets($oSheetFic1).Copy($oCopyTo, Default)

Edited January 28, 2010 by Ahbadiane

[enaiman]

I wonder why don't you use _ExcelReadSheetToArray and _ExcelWriteSheetFromArray ?

That will make it much easier to code and debug.

[Juvigy]

Check this out:

$oExcel=_ExcelBookAttach("C:\some file.xls")
$oExcel.Application.ActiveWorkbook.Sheets("Sheet1").Select
$oExcel.Application.ActiveWorkbook.Sheets("Sheet1").Copy(Default,$oExcel.Application.ActiveWorkbook.Sheets("Sheet1"))
Exit

It is quite faster then

_ExcelReadSheetToArray and _ExcelWriteSheetFromArray

[Ahbadiane]

enaiman,

I need all the information tab (Format, Formula ...)

Juvigy,

Your answer does not copy the worksheet in another Excel file!

Ahbadiane

[Juvigy]

Didnt know you want to copy to another file -here it is :

$oExcel=_ExcelBookAttach("C:\file1.xls")
$oExcel.Application.Workbooks("file1.xls").Sheets("Sheet1").Select
$oExcel.Application.ActiveWorkbook.Sheets("Sheet1").Copy(Default,$oExcel.Application.Workbooks("file2.xls").Sheets("Sheet1"))

Both files needs to be opened first.

[Ahbadiane]

Juvigy,

I still have the same error:

(11) : ==> The requested action with this object has failed.:

$oExcel.Application.ActiveWorkbook.Sheets("Sheet1").Copy(Default, $oExcel.Application.Workbooks($sFile2).Sheets("Sheet1"))

$oExcel.Application.ActiveWorkbook.Sheets("Sheet1").Copy(Default, $oExcel.Application.Workbooks($sFile2)^ ERROR

#include <Excel.au3>
Global Const $sFile1 = "Test1.xls"
Global Const $oSheetFile1 = "Sheet1"
Global Const $sFile2 = "Test2.xls"

Global $oFile1 = _ExcelBookOpen(@ScriptDir & "\" & $sFile1)
Global $oFile2 = _ExcelBookOpen(@ScriptDir & "\" & $sFile2)

Global $oExcel=_ExcelBookAttach(@ScriptDir & "\" & $sFile1)
$oExcel.Application.Workbooks($sFile1).Sheets("Sheet1").Select
$oExcel.Application.ActiveWorkbook.Sheets("Sheet1").Copy(Default, $oExcel.Application.Workbooks($sFile2).Sheets("Sheet1"))

Ahbadiane

[Juvigy]

Works for me as i posted it with 2 excel files open.

Do you get any warnings from the _ExcelBookOpen or _ExcelBookAttach ?

@edit

Tried your version -get the same error. My version still works.

Try my simple test code and see if it works.

@edit2

Try this one;

$sFile2 = "file2.xls"
$sFile1 = "file1.xls"
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open("c:\"&$sFile2)
$oExcel.WorkBooks.Open("c:\"&$sFile1)
$oExcel.Application.Workbooks($sFile1).Sheets("Sheet1").Select
$oExcel.Application.ActiveWorkbook.Sheets("Sheet1").Copy(Default, $oExcel.Application.Workbooks($sFile2).Sheets("Sheet1"))

Edited January 28, 2010 by Juvigy

[Ahbadiane]

Thank you Juvigy

The solution was in order to open two files and create a single object Excel

Ahbadiane