Jump to content
Sign in to follow this  
MarkMontemuro

Excel.au3 The requested action with this object has failed

Recommended Posts

MarkMontemuro

I have an input file which gets stock symbol data from excel. For some reason in the script, The ExcelReadSheetTo Array function radomly throws the error below and terminates the script. If I rerun, it may run fine, If I rerun again, it might error right away. Is there a way to trap this error? or does anyone know why I am receiving this error and have a way around it.

Excel Info:

; Title .........: Microsoft Excel COM UDF library for AutoIt v3

; AutoIt Version : 3.2.3++, Excel.au3 v 1.5 (07/18/2008 @ 8:25am PST)

When I call $aArray = _ExcelReadSheetToArray($oExcel,4,1,51,4) ;

I frequently receive the following error:

C:\Program Files (x86)\AutoIt3\Include\Excel.au3 (823) : ==> The requested action with this object has failed.:

$avRET[$r][$c] = $oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value

$avRET[$r][$c] = $oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value^ ERROR

Share this post


Link to post
Share on other sites
water

Can't test at the moment but I think this happens when the Excel worksheet doesn't have as many rows/columns as you specified.

Can't you just use

$aArray = _ExcelReadSheetToArray($oExcel,4,1)
so the function reads all available rows/columns starting with the one you specified?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
MarkMontemuro

I did initially use the _ExcellReadSheetToArray($oExcel,4,1). That is where I started getting this error. I thought that if I restricted the fields I really wanted, that this would reduce the occurances of this error. It did not.

Share this post


Link to post
Share on other sites
water

Does it work if you just use

$aArray = _ExcelReadSheetToArray($oExcel)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
MarkMontemuro

I updated the script to read: $aArray = _ExcelReadSheetToArray($oExcel,4,1)

1st Symbol went through, then the 2nd failed with:

C:\Program Files (x86)\AutoIt3\Include\Excel.au3 (787) : ==> The requested action with this object has failed.:

Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)

Local $sLastCell = $oExcel.Application.Selection^ ERROR

->14:37:47 AutoIT3.exe ended.rc:1

Share this post


Link to post
Share on other sites
water

Looks like we need some more information about the system you are running:

AutoIt version

Windows version

Windows Bitness (32, 64 bit)

Office version

Offcie Bitness (32, 64 bit)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
MarkMontemuro

AutoIt version V3.3.8.1

Windows version Win 7

Windows Bitness (32, 64 bit) 64 Bit

Office version 2010

Offcie Bitness (32, 64 bit) 32Bit

Tried both versions of AutoIT, both 64 and Native, same results.

Also tried,

$aArray = _ExcelReadSheetToArray($oExcel). Worse results, did not get all of the cell information

Share this post


Link to post
Share on other sites
water

Make sure to compile/run your script for 32 bit.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
MarkMontemuro

Looks like it is running in 32 bit mode. You can see,it runs, then will just randomly error.

>Running:(3.3.8.1):C:Program Files (x86)AutoIt3autoit3.exe "C:Program Files (x86)AutoIt3IBALSSSExacta_Dingo_CME_IB.au3"

Current Time Start: 04/01/2013 15:50:25

#1 Symbol: 3:50:27 PM

#2 Symbol: 3:50:39 PM

#3 Symbol: 3:50:51 PM

#4 Symbol: 3:51:03 PM

#5 Symbol: 3:51:14 PM

#6 Symbol: 3:51:26 PM

C:Program Files (x86)AutoIt3IncludeExcel.au3 (823) : ==> The requested action with this object has failed.:

$avRET[$r][$c] = $oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value

$avRET[$r][$c] = $oExcel.Activesheet^ ERROR

->15:51:30 AutoIT3.exe ended.rc:1

Share this post


Link to post
Share on other sites
jdelaney

Are you doing navigation, or opening/closing sheets? It's possible you are trying to grab the data while not fully loaded.

I'm able to get the same kind of errors when reading a sheet to array, while navigating back and forth between tabs.

It's much harder to create an intentional failure when you actually specify the sheetname:

Func Var_ErrFunc1($oError) ; Com error handling
    ; Do anything here.
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _
            "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oError.description & @CRLF & _
            "err.source is: " & @TAB & $oError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc
Func _ExcelReadSheetToArray2($oExcel, $sSheetName, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = False)
 Local $avRET[1][2] = [[0, 0]] ; 2D return array
 Local $oErrorHandler = ObjEvent("AutoIt.Error", "Var_ErrFunc1")
 ; Test inputs
 If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
 If $iStartRow < 1 Then Return SetError(2, 0, 0)
 If $iStartColumn < 1 Then Return SetError(2, 1, 0)
 If $iRowCnt < 0 Then Return SetError(3, 0, 0)
 If $iColCnt < 0 Then Return SetError(3, 1, 0)
 ; Get size of current sheet as R1C1 string
 ;   Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
 Do
;~   Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
  Local $sLastCell = $oExcel.Sheets($sSheetName).Cells.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
  Sleep(10)
 Until StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 0)
 ; Extract integer last row and col
 $sLastCell = StringRegExp($sLastCell, "\A[^0-9]*(\d+)[^0-9]*(\d+)\Z", 3)
 Local $iLastRow = $sLastCell[0]
 Local $iLastColumn = $sLastCell[1]
 ; Return 0's if the sheet is blank
 If $sLastCell = "R1C1" And $oExcel.Sheets($sSheetName).Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET
 ; Check input range is in bounds
 If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
 If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
 If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
 If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0)
 ; Check for defaulted counts
 If $iRowCnt = 0 Then $iRowCnt = $iLastRow - $iStartRow + 1
 If $iColCnt = 0 Then $iColCnt = $iLastColumn - $iStartColumn + 1
 ; Size the return array
 ReDim $avRET[$iRowCnt + 1][$iColCnt + 1]
 $avRET[0][0] = $iRowCnt
 $avRET[0][1] = $iColCnt
 If $iColShift Then ;Added by litlmike
  ; Read data to array
  For $r = 1 To $iRowCnt
   For $c = 1 To $iColCnt
    $avRET[$r][$c - 1] = $oExcel.Sheets($sSheetName).Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value
   Next
  Next
 Else ;Default for $iColShift
  ; Read data to array
  For $r = 1 To $iRowCnt
   For $c = 1 To $iColCnt
    $avRET[$r][$c] = $oExcel.Sheets($sSheetName).Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value
   Next
  Next
 EndIf
 ;Return data
 Return $avRET
EndFunc   ;==>_ExcelReadSheetToArray
Edited by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites
water

The same problem could be caused by the user editing a cell while the script tries to read this cell.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
MarkMontemuro

These sheets are continually being updated by the server. I was hoping that this would just be taking a snapshot of what was currently in the sheet at the time of the funtion call. It is totally possible that the data is changing. I am not switching sheets though. Cells are being updated as stock prices change. The previous reply is very possible and more than likely the issue. Is there a way to freeze the sheet & then read? Like I said, I just wanted to get a snapshot of all the cell values in the sheet. Is there a better solution?

Share this post


Link to post
Share on other sites
water

Usually you use a database for this kind of data exchange.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

To copy a file which is opened by another application is not a good idea.

The copy might not be in a consistant state.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

I think only the process who opened the WorkBook can do a consistent copy of the data. So if the OP can make the server create a copy from time to time then the rest should b easy.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
MarkMontemuro

I talked to the developer & he will be adding a Stop/Start Feed button which will allow me to take a snapshot without having the cells updated while the ReadSheetToArray function is being executed. Thanks for your input, I think you put me on to solution of the problem.

Share this post


Link to post
Share on other sites
water

Sounds great :D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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  

×