Jump to content

Excel Workbook not closing with _ExcelBookClose


DPAguy
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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.

 

Link to comment
Share on other sites

  • Moderators

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

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

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
Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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.

 

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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.

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...