_Excel_RangeCopyPaste not working with _ExcelBookOpen

[CE101]

Last year when I upgraded my AutoIT system (from vs 3.2.3 to vs 3.3.6.1) I was surprised to find that the IncludeFile "Excel.au3" had substantially changed. Many of the UDFs I had been using were gone and those that remained were renamed. For example    "_ExcelBookNew"  became  "_Excel_BookNew".

Another change was that the Constants were put in a separate Include file (ExcelConstants.au3).

Initially I did not have the time to change and test all my existing programs with the new UDFs. Therefore, as a short-term solution, I copied the old Excel.au3 file into another folder,  renamed it to  ExcelAU3_FromAI-vs323.au3, and referenced it (instead of Excel.au3) in the Include statements.

However going forward I would like to start using the new UDFs, as they contain a lot of nice features.

Today I started with the UDF _Excel_RangeCopyPaste (which did not exist in the old version). However I was disappointed to find that it did not work properly in my exisiting programs. Eventually, after a lot of experimentation, I discovered the problem. It's the way my programs open up Workbooks. They all use the UDF _ExcelBookOpen (from the old IncludeFile). However when I replaced  _ExcelBookOpen  with  _Excel_BookOpen (from the new IncludeFile) the programs worked fine.

TWO QUESTIONS:
(1) What is the reason for this?   Why does _Excel_RangeCopyPaste  behave differently depending on the way the workbook was opened???
(2) Are there other things I should be careful about with respect to the other new UDFs.

Now I am very concerned about implementing the new UDFs. If they would simply crash when the environment is not correct -- that would be ok. If I know there's a problem I can deal with it. However  _Excel_RangeCopyPaste did not crash. It just did not give the desired results in all cases. And usually these subtleties go unnoticed. To catch these problems would take a level of testing that I cannot afford right now.

Any suggestions would be greatly appreciated.

[CE101]

POSTSCRIPT

I just looked inside the two BookOpen UDFs in question (old and new).
As I see it, here is the essential code..

;-----------------------------------
; UDF Old:   _ExcelBookOpen
;-----------------------------------
Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "")
$oExcel = ObjCreate("Excel.Application")
.WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)

;-----------------------------------
UDF New:   _Excel_BookOpen
;-----------------------------------
Func _Excel_BookOpen($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default)
$oWorkbook = $oExcel.Workbooks.Open($sFilePath, Default, $bReadOnly, Default, $sPassword, $sWritePassword)

It seems to me that the only difference is that the new UDF requires that Excel be opened by the calling program.

QUESTION:  Could this explain the behavior of _Excel_RangeCopyPaste ???

Edited July 5, 2016 by CE101

[AutoBert]

11 hours ago, CE101 said:

Last year when I upgraded my AutoIT system (from vs 3.2.3 to vs 3.3.6.1) I was surprised to find that the IncludeFile "Excel.au3" had substantially changed.

I suggest to change to the actual stable 3.3.14.2 as the last script breaking change was in 3.3.14.0 and completly rewritten with 3.3.12.0. I think the support for the  actual version is better guaranted than for the outdated 3.3.6.1 version you have.

[water]

Please check the docu for the change history. There is a link to script breaking changes. Another link leads you to the excel UDF. There you find what was changed and how to move to the new Excel UDF. 

[CE101]

Hi AutoBert and Water:

Thank you for responding.

AutoBert writes:
> I suggest to change to the actual stable 3.3.14.2
> as the last script breaking change was in 3.3.14.0
> and completly rewritten with 3.3.12.0.
> I think the support for the  actual version is better
> guaranted than for the outdated 3.3.6.1 version you have.

Actually I am running 3.3.12.0.  (I upgraded from 3.3.6.1).
However I have not yet gone all the way to 3.3.14.2.
(I have never been an early adopter).

As you suggested, I visited the log  https://www.autoitscript.com/autoit3/docs/history.htm
And I see the following entries (which are relevant to me):

 

VERSION 3.3.14.0 (10th July, 2015) (Release)

• Changed: Minimum OS requirements are now Windows XP SP3 and Windows Server 2003 SP2.
• Fixed #2993: _Excel_RangeFind returns only last occurrence of string found.
• Changed: _Excel_BookOpen() changed from @error = 4 to @error = 0 and @extended = 1 if Workbook could not be opened Read-Write. This is a script breaking change.

VERSION 3.3.12.0 (1st June, 2014) (Release)

• Changed: Excel UDF was re-written. Functions and/or parameters have been renamed, removed or added. This is a script breaking change.

Water writes:
> Another link leads you to the excel UDF.
> There you find what was changed and
> how to move to the new Excel UDF.  

QUESTIONS:
(1) I do not see a link to the UDF. Yes, I would be very interested in seeing the instructions on how to transition to the new Excel UDFs. What is the URL for that?
(2) What does it mean "script breaking change"?   What is so special about the changes that have this designation? Does it mean that a bug that was causing scripts to break was fixed? If so, does that mean that all the Excel UDFs prior to 3.3.12.0 had bugs of this type?

 

[AutoBert]

6 hours ago, CE101 said:

What does it mean "script breaking change"? 

Scripts written in a older version wouldn't run with this func(s). You have rewrite the part(s) of your code using a func which is flaged with "script breaking change".

[water]

You are looking for this: https://www.autoitscript.com/autoit3/docs/script_breaking_changes_excel_udf.htm

And this tutorial in the wiki: http://www.autoitscript.com/wiki/Excel_UDF

[CE101]

Hi AutoBert and Water:    Thank you for the answers.

[water]