Excel.au3 The requested action with this object has failed

[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

[Jos]

Moved to support forum

[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?

[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.

[water]

Does it work if you just use

$aArray = _ExcelReadSheetToArray($oExcel)

[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

[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)

[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

[water]

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

[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

[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:

expandcollapse popup

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 April 1, 2013 by jdelaney

[water]

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

[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?

[water]

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

[Danp2]

Couldn't you make a temporary copy of the spreadsheet and use this for your "snapshot"?

[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.

[Danp2]

Even using the SaveAs feature in Excel to make the copy?

[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.

[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.

[water]

Sounds great