Ah.21

Excel Open and wait for user to close it

11 posts in this topic

I have a script that copy an excel file to temp dir. then open it and when the user close the excel file it should delete it, but the problem is when i use ProcessWaitClose ("EXCEL.EXE"), nothing happens because the process is still open even if the user closed the target excel file, i've tried to check if the file is open but that didnt work, any suggestions??!!

 

#include <Excel.au3>
FileInstall("test.xlsb",@TempDir&"/Temp.xlsb")
; Create application object
Local $oExcel = _Excel_Open
Local $sWorkbook = @TempDir & "/Temp.xlsb"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
if Not FileOpen(@TempDir & "/Temp.xlsb") then ; here where the problem start
If ProcessExists("excel.exe") Then
    ProcessClose("excel.exe")
 EndIf
 EndIf
ProcessWaitClose ("EXCEL.EXE")
FileDelete(@TempDir & "/Temp.xlsb")
Exit

Share this post


Link to post
Share on other sites



Welcome to AutoIt and the forum!

In the Example Scripts Forum you will find a few examples on how to use events. Please check my signature for the link!

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

This example script should do the trick:

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include <Excel.au3>
#include <Constants.au3>
#include <MsgBoxConstants.au3>

; *****************************************************************************
; Example Script
; Handle Excel worksheet change event when a cell has been changed and set the
; color of the cell to red.
; This script loops until Shift-Alt-E is pressed to exit.
; *****************************************************************************
HotKeySet("+!e", "_Exit") ;Shift-Alt-E to Exit the script
MsgBox(64, "Excel UDF: Events Example", "Hotkey to exit the script: 'Shift-Alt-E'!")

; Create application object and open a workbook
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: Events Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookNew($oAppl)
If @error <> 0 Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: Events Example", "Error opening workbook '_Excel2.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf
ObjEvent($oWorkbook, "Workbook_")

While 1
    Sleep(10)
WEnd
Exit

; Excel - Workbook BeforeClose event - https://msdn.microsoft.com/en-us/library/ff839916%28v=office.14%29.aspx
Volatile Func Workbook_BeforeClose(ByRef $bCancel)
    #forceref $bCancel
    If MsgBox(BitOR($MB_YESNO, $MB_ICONQUESTION), "Close?", "Should the Workbook be closed?") = $IDNO Then $bCancel = True
EndFunc   ;==>Workbook_BeforeClose

Func _Exit()
    Exit
EndFunc   ;==>_Exit

 

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Why this only works once?
If I press "No", and then close again, the program exits without show the msgbox.

On 2/10/2015 at 10:44 AM, water said:

This example script should do the trick:

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include <Excel.au3>
#include <Constants.au3>
#include <MsgBoxConstants.au3>

; *****************************************************************************
; Example Script
; Handle Excel worksheet change event when a cell has been changed and set the
; color of the cell to red.
; This script loops until Shift-Alt-E is pressed to exit.
; *****************************************************************************
HotKeySet("+!e", "_Exit") ;Shift-Alt-E to Exit the script
MsgBox(64, "Excel UDF: Events Example", "Hotkey to exit the script: 'Shift-Alt-E'!")

; Create application object and open a workbook
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: Events Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookNew($oAppl)
If @error <> 0 Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: Events Example", "Error opening workbook '_Excel2.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf
ObjEvent($oWorkbook, "Workbook_")

While 1
    Sleep(10)
WEnd
Exit

; Excel - Workbook BeforeClose event - https://msdn.microsoft.com/en-us/library/ff839916%28v=office.14%29.aspx
Volatile Func Workbook_BeforeClose(ByRef $bCancel)
    #forceref $bCancel
    If MsgBox(BitOR($MB_YESNO, $MB_ICONQUESTION), "Close?", "Should the Workbook be closed?") = $IDNO Then $bCancel = True
EndFunc   ;==>Workbook_BeforeClose

Func _Exit()
    Exit
EndFunc   ;==>_Exit

 

 

Share this post


Link to post
Share on other sites

Here it works multiple times. Tested with AutoIt 3.3.12.0 and 3.3.15.0


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I have the v3.3.14.2, maybe should try updating to 3.3.15.0?

Share this post


Link to post
Share on other sites

3.3.15.0 is a beta version, 3.3.14.2 the latest production version.
Do you have a test machine where you can install 3.3.15.0?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

In another computer works fine, but that one is windows 7 and office 2013, mine is windows 10 and office 2016, could be that the problem?

Share this post


Link to post
Share on other sites

Unfortunately I have only tested my scripts and UDFs with Windows 7 and Office 2010.
So yes, it could be caused by Office 2016.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
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