Excel... Copying a sheet from one workbook to another

[CE101]

 

Back in 2012 I wrote a large program that makes extensive use of the UDFs in the then-current "excel.au3" include-file. With the release of AutoIT vs3.3.12.0 (on June1/2014) the "excel.au3" include-file was rewritten. Functions and/or parameters were renamed, removed or added.

Currently I am trying to add some code to the program while leaving all the old UDF's in place and I need some advice on how to copy a sheet from one workbook to another. There is no UDF for this in the old include-file, so I am trying to use the UDF _Excel_SheetCopyMove from the new include-file.

However the SheetCopy operation, the way I have coded it, fails. Here is the relavent code:

$File02   =   @ScriptDir  &  "\Sales2016.xls"
$Sheet_CurrentMonth = "June"  

$oExcelApp = ObjCreate("Excel.Application")
$oExcelApp.Visible = 1
$oExcelWB_X2 = _ExcelBookOpen($File02)
$oExcelWB_X3 = _ExcelBookNew(1)   ; Parm1.1 == Visible

_Excel_SheetCopyMove($oExcelWB_X2, $Sheet_CurrentMonth, $oExcelWB_X3, 1, True)
If @error Then
    UDF104_DebugOut(@ScriptLineNumber, "After _Excel_SheetCopyMove...  @error = " & @error & ",  @extended = " & @extended)
    If IsObj($oExcelWB_X2) = 0  Then
        UDF104_DebugOut(@ScriptLineNumber, "$oExcelWB_X2 is not an object")
    Else 
        PAZ104_DebugOut(@ScriptLineNumber, "$oExcelWB_X2 is an object")
    Endif
    Exit
Endif

I am getting:    @error = 1,  @extended = 0
                       $oExcelWB_X2 is an object   

According to the Help documentation, the syntax and error codes are as follows:

_Excel_SheetCopyMove ( $oSourceBook [, $vSourceSheet = Default [, $oTargetBook = $oSourceBook [, $vTargetSheet = 1 [, $bBefore = True [, $bCopy = True]]]]] )

Error1 =  $oSourceBook is not an object or not a workbook object

QUESTIONS:
(1) Until now I have assumed that $oExcelWB_X2  is a workbook-object. However it seems I am wrong. What is it then?
(2) I don't want to replace the old _ExcelBookOpen udf  with the new one (_Excel_BookOpen), as it causes my calls to other old UDFs to fail. That being the case, perhaps you can suggest some COM code that will create a workbook-object from  $oExcelWB_X2 (as it exists in the program now) that can be used as  $oSourceBook in  _Excel_SheetCopyMove.

Any suggestions would be greatly appreciated.

 

[water]

The old Excel UDF had a lot of limitations. One of them being that you could only work with one workbook at a time.
This limitations have been lifted with the rewrite of the Excel UDF.
Example 3 explains how to copy a worksheet to another workbook.

[CE101]

Hi Water:

Thank you very much for getting back to me.

> The old Excel UDF had a lot of limitations.
> One of them being that you could only work with one workbook at a time.
> This limitations have been lifted with the rewrite of the Excel UDF.

I looked inside the new UDF include-file and see that you authored most of the UDFs and modified many of the others. I also looked at the old file and see that you modified many of the old UDFs as well.  

GREAT WORK!!  

I wish I could make more use of your new UDFs. However for the time being I am stuck with the old ones.

Right now I am making some enhancements to an old program that uses the old UDFs. The program is quite large, over two thousand lines of code. Probably half of it is Excel stuff - COM statements, calls to your old UDFs as well as calls to my own collection of UDFs, which in turn call your UDFs. In addition to this, there are another 10 large programs that make use of your old UDFs and mine.

So let's say in the current program I replace the old _ExcelBookOpen with your new _Excel_BookOpen, many of the calls to my collection of UDFs are going to fail. (I've already tried it). And if I change my own set of UDFs to make them access your new UDFs instead of the old ones, then all my other programs are going to fail unless I make changes to them as well.  It's going to be Living Hell!  And I just don’t have the time to make all these changes and to test everything properly.  
 
Hope this makes sense.

Thank you for the link to the
  https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_SheetCopyMove.htm

Yes I am familiar with that doc.

I just don't know why  the _Excel_SheetCopyMove  udf wont accept my workbook object $oExcelWB_X2 as the source-book.  Is it because it was created with the old BookOpen udf???  And if so, is there any workaround?

Can you suggest some COM code that will create a workbook-object from  $oExcelWB_X2 (as it exists in the program now) that can be used as  $oSourceBook in  _Excel_SheetCopyMove.

Any suggestions would be greatly appreciated.

[water]

I think this is caused by _ExcelBookOpen returning the Excel application object and not the Excel workbook object.
So you would need to pass $oExcelWB_X2.ActiveWorkbook

[CE101]

Thank you for responding.

> you would need to pass $oExcelWB_X2.ActiveWorkbook   

Ok, I changed the statement as follows:

_Excel_SheetCopyMove($oExcelWB_X2.ActiveWorkbook, $SheetName_Current, $oExcelWB_X3.ActiveWorkbook, 1, True, True)

I specified ActiveWorkbook on both sides of the equation - source and target.

I think this is getting me further along in the UDF !!
However this time it fails with
     @error = 5,  @extended = 0

According to the Help doc, the error codes are as follow:
1 - $oSourceBook is not an object or not a workbook object
2 - $oTargetBook is not an object or not a workbook object
3 - Specified source sheet does not exist. Name or index is invalid. @extended is set to the COM error code
4 - Specified target sheet does not exist. Name or index is invalid. @extended is set to the COM error code
5 - Error occurred when copying/moving the sheet. @extended is set to the COM error code

Then I tried

_Excel_SheetCopyMove($oExcelWB_X2.ActiveWorkbook, $SheetName_Current, $oExcelWB_X3, 1, True, True)

 

This time I got  
     @error = 2,  @extended = 0

I understand why it's @error 2. As you pointed out above, _ExcelBookOpen returns the Excel application object and not the Excel workbook object. Therefore my incorrectly-named variable $oExcelWB_X3 is really the application object, not the workbook object. However if that is the case why didn’t $oExcelWB_X3.ActiveWorkbook work?   

Incidentally I added the following statements to test whether my $oExcel variables are objects....

UDF104_DebugOut(@ScriptLineNumber, "IsObj($oExcelWB_X2) = " & IsObj($oExcelWB_X2) )
    UDF104_DebugOut(@ScriptLineNumber, "IsObj($oExcelWB_X3) = " & IsObj($oExcelWB_X3) )
    UDF104_DebugOut(@ScriptLineNumber, "IsObj($oExcelWB_X2.ActiveWorkbook) = " &  _
                                        IsObj($oExcelWB_X2.ActiveWorkbook) )
    UDF104_DebugOut(@ScriptLineNumber, "IsObj($oExcelWB_X3.ActiveWorkbook) = " &  _ 
                                        IsObj($oExcelWB_X3.ActiveWorkbook) )

All four variables show  IsObj($variable) = 1.  
All four variables are objects.

So I am really baffled about $oExcelWB_X3.
What kind of object is it.
If it is an application object, why doesn’t  $oExcelWB_X3.ActiveWorkbook  work.

Any suggestions, on how to get the UDF (_Excel_SheetCopyMove) working would be greatly appreciated.

 

 

 

Edited July 14, 2016 by CE101

[CE101]

Hi Water:

You said earlier that  _ExcelBookOpen returns the Excel application object and not the Excel workbook object.

Does that mean that my program has created two instances of Excel?

(1)   $oExcelApp = ObjCreate("Excel.Application")
        $oExcelApp.Visible = 1
        $oExcelWB_X2 = _ExcelBookOpen($File02)
(2)   $oExcelWB_X3 = _ExcelBookNew(1)   ; Parm1.1 == Visible

If so, perhaps _Excel_SheetCopyMove won't copy a sheet from Workbook2 to Workbook3 because these two workbooks are not in the same instance of Excel.

I just did a google search and I see that some VBA programmers are saying it cannot be done, however I am not sure they have the last word on the subject.
        http://stackoverflow.com/questions/27722675/copy-range-between-two-instances-of-excel
        http://stackoverflow.com/questions/24075427/copy-entire-worksheet-from-one-instance-of-excel-to-another
        http://stackoverflow.com/questions/7867939/how-can-i-copy-between-two-open-excel-instances-in-vba

QUESTIONS:
(1) Has my program created two instances of Excel??
(2) Can a sheet be copied from a workbook in one instance of Excel, to a workbook in another instance???

[water]

1) I would drop this two lines if you do not use $oExcelApp later in your script.
2) Yes

[CE101]

Thank you