hutralospi

Autoit - Closing Excel workbooks.......

4 posts in this topic

#1 ·  Posted

 Hello folks,

I have a random 5 excel files named Workbook, Workbook2..... Workbook5 linked to a program. I wish to arrange all of them side by side for comparison. I do this by using shortcut Alt-W-A-V-Enter through Autoit. [ Figure 111 ]

I wish to close each of the Workbooks WITHOUT saving, using Autoit. I tried to use the following code:

Spoiler

#include <Constants.au3>

HotKeySet("^1", "CloseAllWorkbooks") ; Ctrl+1


While 1
Sleep(100)
WEnd

Func CloseAllWorkbooks()

Sleep(500)

Opt("WinTitleMatchMode",2)

If WinExists("Workbook1") then
WinClose("Workbook1")
WinWaitClose("Workbook1")
Endif

If WinExists("Workbook2") then
WinClose("Workbook2")
WinWaitClose("Workbook2")
Endif

If WinExists("Workbook3") then
WinClose("Workbook3")
WinWaitClose("Workbook3")
Endif

If WinExists("Workbook4") then
WinClose("Workbook4")
WinWaitClose("Workbook4")
Endif

If WinExists("Workbook5") then
WinClose("Workbook5")
WinWaitClose("Workbook5")
Endif

;;;; ProcessClose("Excel.exe")
;;;; WinKill("Microsoft Excel")

EndFunc ;==>CloseAllWorkbooks

 

However, I see that the Windows on the Taskbar are closed, but the actual Workbooks are still present in the desktop and are not closed [ Figure 222 ] .

I tried using both ProcessClose and WinKill [as in the final lines of the above code - by removing the semi-colons one line at a time]. Using ProcessClose, the Excel program does indeed close, BUT, it does not close 'graciously', that is, when Excel is reopened, the files are prompted for recovery [ on the left side of Figure 333 ] . Using WinKill too, the Excel program gets closed, but it appears to be Crashing. The program linked to these files (mentioned on the very first line of this post) also crashes due to this excel crash, and stops working.

How do I close each workbook smoothly without saving them? I can press 'n' when prompted for saving.

I have not used the Excel.au3 function yet. (I looked it a little, but unable to deduce anything with my available short time.) Would the above code need it? If so, can you help by using the appropriate calls like _Excel_BookClose or _Excel_Close in the above code?

Thanks for your time.

-Dave

 

333.jpg

222.jpg

111.jpg

Share this post


Link to post
Share on other sites



#2 ·  Posted

You would need something like this to close all open workbooks:

#include <Excel.au3>
Global $oExcel = _Excel_Open(False) ; Connect to Excel and remain invisible
Global $aWorkbooks = _Excel_BookList($oExcel) ; Return a 2D array containing a list of open workbooks
For $i = 0 To UBound($aWorkbooks, 1) - 1 ; Close all found workbooks
    _Excel_BookClose($aWorkbooks[$i][0], False)
Next
_Excel_Close($oExcel, Default, True) ; Close the Excel instance even when not created by the script

 

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

#3 ·  Posted

@water,

Wow! Fantastic! It works!

Thanks a LOT for taking time to help me out.  You have made my day.

Blessings.

 

Share this post


Link to post
Share on other sites

#4 ·  Posted

Glad to be of service :)


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