Having problems with new Excel UDF

[water]

The exception error is very, very strange. Need to do more investigation!

The A1 fromat has been chosen to reduce the number of necessary parameters. So I fear you have to live with the messy syntax

[ijourneaux]

I have no problem following the concensus opinion. I just do so much programming in vba in Excel where both interfaces are used to interact with the worksheet. There I prefer row column.

That said, I am happy that the UDF is updated and supported.

Looking forward to hearing from you tomorrow after you hav ehad a chacne to look at the issue with Excel 2007.

Ian

[water]

I downloaded your test file from post #10 and the script from post #13 and it works just fine. Tried with Excel running and not running when starting the script.

I'm running Office 2010 32 bit on Windows 7 64bit.

Is there anything special in your environment (Windows, Office)?

Do you have any add-ins in Excel?

I once had a problem with a badly written Word add-in.

Could you please add a Sleep(20000) after _Excel_Open and _Excel_BookOpen to check for startup problems?

[water]

I just played a litte bit longer with the problem.

When the "Summary" sheet didn't exist then I too get the message:

 

@@ DEBUG COM Error encountered in Test.au3 (1056) :
    Number            = 0x80020009 (-2147352567)
    WinDescription    = Ausnahmefehler aufgetreten.
    Description       =
    Source            =
    HelpFile          =
    HelpContext       = 0
    LastDllError      = 0
    Retcode           = 0x8002000B

 

But in addition I get the following error message:

 

_Excel_SheetDelete: $iReturnValue = 0, @error = 2, @extended = -2147352567

So this error seems to be OK.

[ijourneaux]

For me, here is the reproducible pattern.

1. If Excel is not running when the script is started, the summary sheet is deleted as expected and excel is successfully closed when the script completes

2. If excel is running  with no worksheet loaded, the script causes Excel to become hidden, the sheet is not deleted and when the script terminates, there is still a copy of Excel left in task manager. The worksheet is locked until the Excel task is killed off.

I do have a couple of Excel add-in installed (related to the Aspentech process historian IP.21). Adding the sleep statements didn't change the behavior. 

I changed the _Excel_open statement to 

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

to force a new instance of Excel to be created and the script works as it should. In my case, that is probably the best option anyway as if the user has a worksheet open I wouldn't want it to be made invisible.

That raises the question as to what to do on Open and Close. If Excel is visible when the script starts and the UDF is set to not create a new instance and to hide Excel then when _Excel_Close is called Excel should be made visible again. Perhaps if the AutoIt script is configured to hide excel and Excel is alreay open, the default should be to create a new instance.

[water]

That's what the value returned in @extended by _Excel_Open is for.

0 - Excel was already running

Before calling _Excel_Close you could set the application to visible again by using:

$oExcel = _Excel_Open(False)
$iVisible = @extended
; Rest of your code
If $iVisible = 0 Then $oExcel.Visible = True
_Excel_Close()

Edited July 14, 2014 by water

[ijourneaux]

So, although my problem (Summary sheet not being deleted if Excel is running) has been solved by using 

 

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

 

why does it behave differently that my original line 

 

$oExcel = _Excel_Open(False)

[water]

I have absolutely no idea

I tested with Excel 2010 and your test file always gets the Summary sheet deleted on first try. No matter if Excel was running or not.

It is either related to the Excel version you run or the environment (Excel addons, Virus scanner ...).

Why on earth would Excel delete a sheet only every second or third try?

Is it possible to test your script on another computer?