Working with a ShellExecute failure

[jerseyzuks]

Trying to verify that Excel can open a previously created file

Here is the code that I wrote, and it works exactly the way I wanted when it passes

$OpenExelDoc = ShellExecute (@MyDocumentsDir & "\compatibility.xls")

If $openExelDoc = 0 Then

MsgBox(0, "Error", "Excel was not able to open the file")

FileWriteLine($file, "FAIL - Excel was not able to open the Previously Saved spreadsheet")

elseif $OpenExelDoc = 1 Then

FileWriteLine($file, "PASS - Excel Open Previously Saved Document and Verified it was in the .xls format")

sleep(1500) ;pause for 1 and 1/2 second

Send("!{F4}") ;Sends the ALT+F4 function to close the window

Sleep(1500) ;pause for 1 and 1/2 second

FileDelete(@MyDocumentsDir & "\compatibility.xls") ;tells the script to delete the file it just created from the myDocuments folder

Endif

Now, here is the issue. If it passes, the code is fine. When I intentionally break it to simulate a problem, an error message pops up before it actually "Fails" and returns the failure. Until I click the "Windows cannot find 'C:\xxxxxxxx" error message, the script will not continue.

I thought I could get around it with a winexists prior to the ShellExecute command, but it doesn't seem to be picking up that window at all.

[BrewManNH]

Try using FileExists to see if it's there instead of trying to open it with ShellExecute, if it's there then you can open it, if it's not, you won't get any pop up.

[jerseyzuks]

_{I do have a FileExists , and that is working perfectly as well. The test criteria I am trying to simulate is as following...}

_{Open Excel (no issue here)}

_{Enter data into a spreadsheet (again, workes fine)}

_{Print the spreadsheet (fine)}

_{Save the spreasheet and close (no problemo)}

_{Verify the file exists, and is in the correct directory (works perfect)}

_{Verify the file can be opened from Excel without error (ah... busted... so close but so far)}

Edited May 22, 2012 by jerseyzuks

[BrewManNH]

When it's failing, why is it failing? Because you've moved/renamed the file?

[water]

Why don't you use the Excel UDF that comes with AutoIt?

[jerseyzuks]

When it's failing, why is it failing? Because you've moved/renamed the file?

To simulate a failure, I rename the file, and comment out the FileExists line. It doesn't matter what the error message is, if there is an error, it is a failure.

Once this code is hammered out, this will be used as a template for other applications (word, powerpoint, in house apps, etc)

Basically we need to make sure all of our baseline apps still work after we make any changes to policies, software upgrades, hardware changes, etc.

[BrewManNH]

Honestly, testing it that way is stupid. You're deliberately breaking the script and expecting it to work? The FileExists was there to check to see if the file exists, then you try to open a file that doesn't exist? Can't see how that's any way to idiot proof a script.

[jerseyzuks]

Honestly, testing it that way is stupid. You're deliberately breaking the script and expecting it to work? The FileExists was there to check to see if the file exists, then you try to open a file that doesn't exist? Can't see how that's any way to idiot proof a script.

I am not "deliberately breaking the script".

I am deliberately generating an error in Excel, to see if I can capture that as a failure. If Excel fails to open a file for any reason, it needs to be noted.

If you have a better suggestion as to how to recreate a failure, I am all ears. That was just the first one that popped into my head.

[Reg2Post]

But FileExists will tell you why any application cannot open a file if the file does not exist.

Maybe trying to open the non-existing file with an Excel UDF (or a translated VBA function) function will just return an error code instead of an error message window.

[water]

The Excel UDF uses the COM interface and if a COM error handler is set up returns details error information.

[BrewManNH]

You are deliberately breaking how the script is working by removing the error checking and then creating an error and trying to find a way to compensate for an error you shouldn't have in the first place. If the file doesn't exist, which you checked for with FileExists, don't try and open it, seems simple enough to me. If you're going to ignore the fact that the file doesn't exist and still try and open it, then you are only making more work for yourself to try and fix later.

[jerseyzuks]

OK, lets take a couple of steps back... Forget we are talking about Excel specifically, and forget what is causing the error message for a moment...

I have a file I want to open with the ShellExecute command, and then if it succeeds, follow one if/then statement, and if it fails follow another

When it succeeds, it works fine

When it "fails", it does't report a fail until I click the "OK" on the message box (so the script won't continue)

What would be the best way to handle this?

[BrewManNH]

FileExists, see above.

[jerseyzuks]

FileExists, see above.

Yes, I fully understand that FileExists will show me if the file exists.

What I am trying to do is see if there are any errors when it attemps to open the file (permissions, corrupt file, anything).

And this code is going to be used for numerous applications, not just excel, so I need it to be as portable as possible

[BrewManNH]

Extensive error checking is better than dealing with errors. ShellExecute is probably not the best way to open files unless you know you're able to open them and they aren't going to cause an error condition. If you get an error, you deal with it by looking for the error window with WinExists or WinWait with a time out and clicking past it. If you are specifically looking to open files and you know which file types you're looking to open, you can always open them directly using Run most of the time.

[jerseyzuks]

Extensive error checking is better than dealing with errors. ShellExecute is probably not the best way to open files unless you know you're able to open them and they aren't going to cause an error condition. If you get an error, you deal with it by looking for the error window with WinExists or WinWait with a time out and clicking past it. If you are specifically looking to open files and you know which file types you're looking to open, you can always open them directly using Run most of the time.

So why bother including error handling on ShellExecute?

99.9% of the time, we expect all of these files to open normally. They are .txt files, .xls files, .pdf files, .doc files, etc.

If, for some reason they don't open normally, we have someone investigate manually. I just need to figure out a "catch" for the rare instance something doesn't open the way it is supposed to

[Mechaflash]

You could use FileOpen() after FileExists() to test for access rights/attributes. If it returns >= 0 then FileClose() and ShellExecute().

[Mechaflash]

So why bother including error handling on ShellExecute?

If you don't mind the windows popup if it fails for whatever reason... and you don't mind the user having to deal with that error as opposed to the program dealing with it... then who cares?

ShellExecute away without error handling.