Excel - The requested action with this object has failed

[DigDeep]

Hi,

Out of 10 times I run the application I get atleast 5-6 times the error The requested action with this object has failed. I have tried to keep Excel open close after every action too but it does not help.

This happens at line Local $iCountLines = $oWorkbook.ActiveSheet.UsedRange.Rows.Count

Any suggestion on the cause and fix for this please?

Test.au3

[robertocm]

34 minutes ago, DigDeep said:

Any suggestion

Try setting visible parameter as true

Global $oExcel = _Excel_Open(True, False, False, Default, True)

Avoid 'double dot notation':

;Instead of:
;Local $ColNum = $oWorkbook.ActiveSheet.UsedRange.Columns.Count

;Try something like (not tested):
Local $oSheet = $oWorkbook.ActiveSheet
Local $oRange = $oSheet.UsedRange
Local $oColumns = $oRange.Columns
Local $ColNum = $oColumns.Count

;Then replace: $oWorkbook.ActiveSheet
;whith: $oSheet

Test different ways to detect last column: alternatives to UsedRange. Just an idea:

$oSheet.Range("Print_Area")

Another possible test: add this at the beginning:

;Excel UDF - AutoIt Wiki
;Script crashes
;When your script processes a large amount of workbooks in a loop and suddenly crashes without error then try to run your script in 64 bit mode by adding
#AutoIt3Wrapper_UseX64=y

[water]

Hard to tell.
Some things to consider:

• You do not do any error checking after calling a function like _Excel_BookOpen etc. So errors go unnoticed and might crash your script many steps later
• Your code could be enhanced:
  • You redefine variables (e.g. $sTab7) outside and inside a loop
  • In function Column_GetValues you open a workbook but you never close it
  • In  the same function you open the Excel application again - there is no need for this. Open and close the application once in your script
  • Smae function. _Excel_BookClose($o_Excel) is wrong. You pass the Excel application object, but the function needs a workbook object. As you do not check for errors this problem remains unnoticed.

If you can tell us what you try to achieve we might help you to write more stable and clean code

 

[DigDeep]

@robertocm, This was an example one I had provided with few changes and my prod version is in 64-bit. But I will give a try with your other suggestions.

@water, in my Prod there are error checks at every step I am taking if any issues while opening Excel or workbook. Also while deleting any lines. For other steps I'll take a look.

[water]

Always a good idea to add such information to the first post