DPAguy

Excel Workbook not closing with _ExcelBookClose

13 posts in this topic

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.

Share this post


Link to post
Share on other sites



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

1 person likes this

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by water
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 :angry:.

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

 

Share this post


Link to post
Share on other sites

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

@OP did you try to close the workbook with

$excel1.Close()

?

 

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by AutoBert

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now