Excel Workbook not closing with _ExcelBookClose

[DPAguy]

I've been testing scripts for compatibility since our company upgraded from WinXP/Office 2007 to Win7/Office 2010. I am using AutoIt v3.3.0.0.

This script is for automating an export from one of our legacy systems, saving the data as extract.xls, consolidating it on GroupExtract.xls, then closing the extract.xls so it can be overwritten for the next pass of the loop. The part of the script below is currently not working because the _ExcelBookClose function is not closing the extract.xls workbook, resulting in multiple read only files popping up and duplication on the GroupExtract.xls. 

$excel1 = _excelbookopen("G:\...\extract.xls")
        if $count = 1 Then
            $excel2 = _excelbookopen("G:\...\GroupExtract.xls") 
        endIf
        _ExcelSheetActivate($excel1,"Sheet1")
        sleep(500)
        $excelarray=_ExcelReadSheetToArray($excel1, 2) 
            sleep(500)
        _ExcelWriteSheetFromArray($Excel2, $excelArray, $pasterow, 1) 
        $pasterow = $pasterow + $excelarray [0][0] 
        _ExcelBookClose($excel1, 0, 0)

We didn't have any issues with this script until the system upgrade a few days ago. This feels like it should be a trivial issue but I still haven't been able to find a resolution!

Any help would be appreciated.

[AutoBert]

I think it's a problem with the read/write permissions, so contact your sysAdmin to correct them.

[Jfish]

That is an older version of the Excel UDF.  I would recommend upgrading your version of AutoIt and using the new Excel functions.

[water]

Note that using the newest version of the Excel UDF needs to check/adapt your scripts because there have been a lot of script breaking changes.
In your old code I would at least add some error checking. After each _Excel* function you need to check that here was no error and only keep on processing when no error occurred.
NB: You can drop the Sleep statements because the functions work synchronous.

Edited January 29, 2016 by water

[DPAguy]

Thanks for the replies guys. Would you be able to advise which section of write permissions we should be looking into? Things have become a lot more locked down since we upgraded to Win7 so we'll have to build a case for our sysAdmin to correct anything. Lots of red tape around here for all things IT related which is the same reason why we're using such and old version of AutoIt. I'm not really expecting to get the green light on upgrading to the current version anytime soon. Big company problems .

PS thanks for the advise re: dropping the Sleeps! That'll save a bunch of time with some of the other scripts we have.

 

[JLogan3o13]

@DPAguy are you able to do this process manually with no issues (including saving and closing the workbook)? And is it running under your user account? If the answer is yes, it is not a permissions issue.

[PACaleala]

@OP did you try to close the workbook with

$excel1.Close()

?

 

[AutoBert]

Quote

 

Meanwhile, after looking in old help 3.3.8.1 (german translation), i don't realy believe in write permissions, because you used the param for not saving before close.

The Drive G: is a local drive or a network drive? 

But @water do you know how excel locks files in use. Is this like word does, creating a lockfile which is deleted when closing? When this is the way: all user's have to close and the admin has to delete all these lock files. After them all should runing without problems.

Edited January 29, 2016 by AutoBert

[water]

48 minutes ago, PACaleala said:

@OP did you try to close the workbook with

$excel1.Close()

?

That is what _ExcelBookClose does under the covers.

DPAguy,

what is the value of @error after _ExcelBookClose?

[AutoBert]

5 minutes ago, AutoBert said:

... @water: do you know how excel locks files in use. Is this like word does, creating a lockfile which is deleted when closing? When this is the way: all user's have to close and the admin has to delete all these lock files. After them all should runing without problems.

 

[water]

I'm not sure how Excel handles file locking. But as it is possible that several people work on the same Workbook (but on different sheets) - when the Workbook is set to shared use - I think Excel is more like a database and uses some internal locking mechanism.

[DPAguy]

@JLogan3o13 and @Autobert, I have no issues with saving and closing the workbooks manually. The G: drive is a network drive.

@water, that's the strange thing. There's no error message from AutoIt when it hits the _ExcelBookClose, it just moves on to the next step of the loop. What ends up happening is the script repeatedly opens the extract.xls file in read-only each instance of the loop, preventing me from replacing the file each time the loop hits the export function.

[water]

_Excel* functions do not display an error message, they set @error or the return value as described in the help file. You have to check for errors in your script.