Jump to content
Sign in to follow this  
Ahbadiane

[RESOLVED] Copy Excel sheet

Recommended Posts

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 by Ahbadiane

Share this post


Link to post
Share on other sites
enaiman

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

That will make it much easier to code and debug.


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


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

Share this post


Link to post
Share on other sites
Ahbadiane

enaiman,

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

Juvigy,

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

Ahbadiane

Share this post


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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites
Ahbadiane

Thank you Juvigy

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

Ahbadiane

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  

×