Excel Open and wait for user to close it

[Ah.21]

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

[Jfish]

There is an event that you should be able to hook immediately prior to the workbook closing: https://msdn.microsoft.com/EN-US/library/office/ff836770.aspx

It may be cleaner than looking for the application process and forcing it to close.

[water]

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!

[Jfish]

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!

Very cool!

[water]

This example script should do the trick:

expandcollapse popup

#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

 

[Gianfranco_753]

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:

expandcollapse popup

#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

 

 

[water]

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

[Gianfranco_753]

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

[water]

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?

[Gianfranco_753]

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?

[water]

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