Bug ?? Excel.udf $oWorkbook1.ActiveSheet.Paste fail to work on Excel 365

[KhooYong]

Hi all,

First Post. I notices that using Excel.udf  on Excel 365, the command $oWb.ActiveSheet.Paste will fail to work.

The script copies from Clipboard and paste to excel worksheet.

The script below works fine on Excel 2010 and 2013 but not on Excel 365. (Not sure about Excel 2016). 

Any work around for this or bug patch?

#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xlsx")
$oWorkbook1.Activesheet.Range("A1").Select ;Select the appropriate cell in the worksheet you want
;"Select and copy anything ,eg. from excel worksheet or any web site.
$oWorkbook1.ActiveSheet.Paste  ;<= Problem
; Excel 2010 to 2013 ok, Excel 365 will fail. The operation fail on this object.

 

 

regards..Khoo Yong

[water]

Can you please post the error message you get from ScITE?
Or even better: the value of @error after .Paste?

[KhooYong]

Hi water,

Thanks.

Code that I ran using Windows 10 and Excel 365 with error.

Same code runs fine using Windows 10 and Excel 2010 /Excel 2013

 

ScITE window  (test.3au3.au3)

------------------------------------------------------------

#include <MsgBoxConstants.au3>

#include <Excel.au3>

Global $oExcel = _Excel_Open()

Global $sWorkbook = @ScriptDir & "\OOC_Chartlist.xlsx"

Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

$oWorkbook.ActiveSheet.Range("A2").Select

$A=ClipGet()

MsgBox("","",$A)

$oWorkbook.ActiveSheet.Paste

Sleep(1000)

 

Console Output window 

------------------------------------------------------

>"C:\Program Files (x86)\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\GFApps\1_Daily_Setup_OOC_Report_Full_Auto_Std_Template_S72\AutoIT\test.3au3.au3"    

"C:\GFApps\1_Daily_Setup_OOC_Report_Full_Auto_Std_Template_S72\AutoIT\test.3au3.au3" (10) : ==> The requested action with this object has failed.:

$oWorkbook.ActiveSheet.Paste

$oWorkbook.ActiveSheet^ ERROR

>Exit code: 1    Time: 2.322

-------------------------------------------------------------

- Was not able to get error code. I tried 

If @error then

  Msgbox("","", @error) 

endif

But it does not run at all.

 

 

 

 

[Nine]

Add a com error handler :

Global $oHandler = ObjEvent("AutoIt.Error", ErrFunc)

; Your code goes here

; This is a custom error handler
Func ErrFunc($oError)
    MsgBox($MB_OK, "We intercepted a COM Error !", _
            "Number: 0x" & Hex($oError.number, 8) & @CRLF & _
            "Description: " & $oError.windescription & _
            "At line: " & $oError.scriptline & @CRLF)
EndFunc   ;==>ErrFunc

 

[KhooYong]

Hi Nine,

Thanks . This is the revise code with error msg

#include <MsgBoxConstants.au3>

#include <Excel.au3>

Global $oHandler = ObjEvent("AutoIt.Error", ErrFunc)

Global $oExcel = _Excel_Open()

Global $sWorkbook = @ScriptDir & "\OOC_Chartlist.xlsx"

Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

$oWorkbook.ActiveSheet.Range("A2").Select

$A=ClipGet()

MsgBox("","",$A)

$oWorkbook.ActiveSheet.Paste

If @error = 1 Then

   ConsoleWrite("Error" & @error & @CRLF)

endif

Sleep(1000)

Exit /0

 

Func ErrFunc($oError)

    MsgBox($MB_OK, "We intercepted a COM Error !", _

            "Number: 0x" & Hex($oError.number, 😎 & @CRLF & _

            "Description: " & $oError.windescription & _

            "At line: " & $oError.scriptline & @CRLF)

EndFunc   ;==>ErrFunc

 

------------------------------------------------------------

MsgBox is

-------------------------------------------------------

We intercepted a COM Error!

Number:0x80020009

Description: Exception occurred.

At line:11

---------------------------------------------------------

 

 

 

 

 

 

 

[Subz]

Works fine for me on Office 365 but only if clipboard has data type compatible with paste, for example copy text or another cell in Excel and it works fine.  If you use an unsupported type for example you copy a file and you'll produce the results above.

Edited March 12, 2022 by Subz

[KhooYong]

Hi Subz,

- Thanks

-  You are absolutely right when you want to copy and paste from Excel to  ClipBoard to Excel. The Paste command still works. 

- However , what I meant was that the command  will break when copying using Clipboard from say text application notebook or web page. E.g.

E.g.   "a    b

           c     d" to clipboard then paste to excel. 

 

-  Sorry , I did not make it clear that I was not copying from Excel to Clipboard to Excel, as there are much easier command to do so e.g.  Excel_RangeCopyPaste 

    for that. 

 

 

 

 

 

 

[Nine]

Please use tags when you post code, as described in the link.

The @error returned after an exception is not 1, but the number displayed in the handler, so just test :

If @error Then
   ConsoleWrite("Error" & @error & @CRLF)
endif

 

[Subz]

Don't have an issue when copying text including your example above, it appears your data is being changed, you could use something like:

;~ Change clipboard data to text
ClipPut(String(ClipGet()))

;~ Or

;~ Check clipboard data is text, on error change to error text
Global $vClipGet = ClipGet()
If @error Then ClipPut("N/A")

 

[KhooYong]

Hi Nine ,

Thanks . I have updated 

 

; Script Start - Add your code below here
#include <MsgBoxConstants.au3>
#include <Excel.au3>
Global $oHandler = ObjEvent("AutoIt.Error", ErrFunc)
Global $oExcel = _Excel_Open()
Global $sWorkbook = @ScriptDir & "\OOC_Chartlist.xlsx"
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
$oWorkbook.ActiveSheet.Range("A2").Select
ClipPut("")
ClipPut("a b c d ")
MsgBox("","",ClipGet())
$oWorkbook.ActiveSheet.Paste
If @error Then
   ConsoleWrite("Error" & @error & @CRLF)
endif
Sleep(1000)

Func ErrFunc($oError)
    MsgBox($MB_OK, "We intercepted a COM Error !", _
            "Number: 0x" & Hex($oError.number, 8) & @CRLF & _
            "Description: " & $oError.windescription & _
            "At line: " & $oError.scriptline & @CRLF)
EndFunc   ;==>ErrFunc

 

This is the console result

-----------------------------------------------------------------------------

>"C:\Program Files (x86)\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\GFApps\1_Daily_Setup_OOC_Report_Full_Auto_Std_Template_S72\AutoIT\test.3au3.au3"    

 

Error-2147352567

 

>Exit code: 0    Time: 4.954

 

Hi Subz,

 

I tried to use ClipPut(String(ClipGet())) but the error remains the same. I even tried to put a fixed value using ClipPut("a b c d") but the result still remains same. (Error as above).

 

 

 

[Nine]

Have you tried the Range.PasteSpecial method instead ?  This will allow you much flexibility...

[Subz]

a. Does the spreadsheet open?
b. When it fails and you press Ctrl+V does it paste the content?
c. What happens if you test on a new workbook (not an existing workbook)?

[KhooYong]

Hi Nine,

Thanks . This is my take on PasteSpecial and it does not seems to work .Perhaps I got the syntax wrong. I tried both ways , 

using $oExcel gives me exactly the same error while $oWorkbook gives me a different error.   

 

; Script Start - Add your code below here
#include <MsgBoxConstants.au3>
#include <Excel.au3>
Global $oHandler = ObjEvent("AutoIt.Error", ErrFunc)
Global $oExcel = _Excel_Open()
Global $sWorkbook = @ScriptDir & "\OOC_Chartlist.xlsx"
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
$oWorkbook.ActiveSheet.Range("A2").Select
ClipPut("")
ClipPut("a b c d ")
MsgBox("","",ClipGet())
$oWorkbook.ActiveSheet.Paste  ;=> Com Error Number :0x80020009 Description :Exception occurred at line 12:Error -2147352567 (original error)
;$oExcel.Selection.PasteSpecial("Text")   ;=> Com Error Number :0x80020009 Description :Exception occurred at line 13:Error -2147352567 (Same as before)
;$oWorkbook.Selection.PasteSpecial("Text") ;=> Com Error Number :0x80020006 Description :Unknown name at line 14:Error 1


If @error Then
   ConsoleWrite("Error" & @error & @CRLF)
endif
Sleep(1000)

Func ErrFunc($oError)
    MsgBox($MB_OK, "We intercepted a COM Error !", _
            "Number: 0x" & Hex($oError.number, 8) & @CRLF & _
            "Description: " & $oError.windescription & _
            "At line: " & $oError.scriptline & @CRLF)
EndFunc   ;==>ErrFunc

 

 

 

[KhooYong]

Hi Subz,

a. Yes , the spreadsheet opened.

b. Yes, I press Ctrl+v and it does paste the content

c. I replace my current workbook with below (ie create new workbook) and the same error occurs for both ActiveSheet.Paste and PasteSpecial.

Global $sWorkbook = _Excel_BookNew($oExcel, 2)

 

[Nine]

I got some problem too with PasteSpecial.  But this seems to solve the issue :

#include <Excel.au3>

Global $oHandler = ObjEvent("AutoIt.Error", ErrFunc)
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel, 1)
ClipPut("a b c d ")
_Excel_RangeWrite($oWorkbook, Default, ClipGet(), "A2")
If @error Then ConsoleWrite(@error & @CRLF)

Func ErrFunc($oError)
    MsgBox($MB_OK, "We intercepted a COM Error !", _
            "Number: 0x" & Hex($oError.number, 8) & @CRLF & _
            "Description: " & $oError.windescription & _
            "At line: " & $oError.scriptline & @CRLF)
EndFunc   ;==>ErrFunc

 

Edited March 12, 2022 by Nine

[KhooYong]

Hi Nine,

Thanks. Yes, this does seems to work in its own way. 

However, the original format (i.e. ) in the form of table seems to be complete lost as it converts to a string.

Yes, I do know that I can use the function StringSplit2D()  to get it to copy to an array then use Rangewrite to update to Excel but my current table format has a CR on one of the cell .ie "aaa (@CR) aa" which makes for an awkard conversion from ClipGet()  via StringSplit2D()  to RangeWrite on Excel.

It is doable but not an universal solution as ActiveSheet.Paste in Excel 2010 because for different data you need to tweak the delimiter in Excel 365.

 

  -> Imperfect solution below ( With some tables , I have to tweak the row delimiter  so that the columns align correctly (due to some cells having @CR in them) , which is not the case with paste. )

#include <StringConstants.au3>
#include <Array.au3>

Global $g_sString   = ClipGet()
Global $g_sDelimRow = @LF
Global $g_sDelimCol = @TAB

Global $g_aArray2D  = StringSplit2D($g_sString, $g_sDelimCol, $g_sDelimRow)
_ArrayDisplay($g_aArray2D, @ScriptName)

Func StringSplit2D($sMatches = "Hola-2-5-50-50-100-100|Hola-6-200-200-100-100", Const $sDelim_Item = "-", Const $sDelim_Row = "|", $bFixLast = Default)
    Local $iValDim_1, $iValDim_2 = 0, $iColCount

    ; Fix last item or row.
    If $bFixLast <> False Then
        Local $sTrim = StringRight($sMatches, 1)
        If $sTrim = $g_sDelimRow Or $sTrim = $sDelim_Item Then $sMatches = StringTrimRight($sMatches, 1)
    EndIf

    Local $aSplit_1 = StringSplit($sMatches, $sDelim_Row, $STR_NOCOUNT + $STR_ENTIRESPLIT)
    $iValDim_1 = UBound($aSplit_1, $UBOUND_ROWS)
    Local $aTmp[$iValDim_1][0], $aSplit_2
    For $i = 0 To $iValDim_1 - 1
        $aSplit_2 = StringSplit($aSplit_1[$i], $sDelim_Item, $STR_NOCOUNT + $STR_ENTIRESPLIT)
        $iColCount = UBound($aSplit_2)
        If $iColCount > $iValDim_2 Then
            $iValDim_2 = $iColCount
            ReDim $aTmp[$iValDim_1][$iValDim_2]
        EndIf
        For $j = 0 To $iColCount - 1
            $aTmp[$i][$j] = $aSplit_2[$j]
        Next
    Next
    Return $aTmp
EndFunc   ;==>StringSplit2D

 

 

 

 

 

 

[jugador]

@KhooYong

#include <Excel.au3>
#include <Array.au3>

__Method1()
Func __Method1()
    Local $o_Excel = _Excel_Open()
    If @error Then Exit

    Local $o_Workbook = _Excel_BookNew($o_Excel, 1)
    If @error Then
        _Excel_Close($o_Excel)
        Exit
    EndIf

    ;~ Method 1
    ClipPut("a b c d ")
    _Excel_RangeWrite($o_Workbook, Default, ClipGet(), "A1")
    ClipPut("")

    ;~ Method 2
    ClipPut("a b c d ")
    $o_Workbook.ActiveSheet.Range("A2").PasteSpecial
    ClipPut("")

    ;~ Method 3
    ClipPut("a b c d ")
    $o_Workbook.ActiveSheet.Range("A3").Select
    $o_Workbook.ActiveSheet.paste
    ClipPut("")


    MsgBox(0, "", "Click to continue.....")
    _Excel_BookClose($o_Workbook, False)
    _Excel_Close($o_Excel)
    Exit
EndFunc

note: code tested on Excel 2007

Edited March 12, 2022 by jugador

[KhooYong]

Hi Jugador,

Thanks . The problem/bug was in reference to Excel (Office 365).  ActiveSheet.Paste has no problem working on Excel 2010 and 2013 as well.

 

 

[Subz]

I'm running Microsoft 365 Apps for Enterprise v2108 64-bit and is able to run the scripts above without an issue.  Can you record a macro in Excel and show us what the results are for the new macro?

[KhooYong]

Hi ,

This is the Microsoft version I am running

Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20782) 64-bit

Version 2108. (Build 14326.20784 Click-to-Run) 

I tried running the script above by Subz 

As expected Method 1 works (see post 9) , except the format is not table base(if use this method, has to convert to Array2D with its limitation) . Method 2 and Method 3 does not work.

(See cell A2 and A3 are empty) 

 

regards..Khoo Yong