Excel UDF, How to get excel to close after user close open excel document

[HighlanderSword]

Hello,

I have the below code and am running into challenges trying to get excel to close after user closes Excel , the Excel process stays running in the background and

will not stop running until the entire script is exited out of,

What steps do I need to do to close out the Excel background task after the user closes the Excel doc 

 

expandcollapse popup

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

Global $ooutlook
InitOutlook()


Func InitOutlook()
    ; Launch Outlook if not already running.
    $sRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\OUTLOOK.EXE"
    $sOutlookPath = RegRead($sRegKey, "")


    If ProcessExists("outlook.exe") Then

Else

ShellExecute($sOutlookPath)

    Do

    Until ProcessExists("outlook.exe")
    Sleep(5000)
    Sleep(5000)


EndIf

    $ooutlook = _OL_Open()

EndFunc

Func cal_list()

 local  $aFolder = _OL_FolderAccess($oOutlook, "", $olFolderCalendar)
    Global $aFolder = _OL_FolderAccess($oOutlook, "", $olFolderCalendar)

    ; Today's date filter
    Local $sToday = @YEAR & "-" & StringFormat("%02d", @MON) & "-" & StringFormat("%02d", @MDAY) & " 00:00"


    Global $aAppointments = _OL_ItemFind($oOutlook, $aFolder[1], $olAppointment, "[Start]>='" & $sToday & "'")

    ; Open Excel
    Global $oExcel = _Excel_Open()
    Global $oWorkbook = _Excel_BookNew($oExcel)
    Local $oSheet = $oWorkbook.Sheets(1)

    ; Headers
    $oSheet.Cells(1, 1).Value = "Subject"
    $oSheet.Cells(1, 2).Value = "Start Time"

    ; Fill data
    For $i = 1 To UBound($aAppointments) - 1
        $oSheet.Cells($i + 1, 1).Value = $aAppointments[$i][3] ; Subject
        Local $dt = ConvertDateStringToExcelSerial($aAppointments[$i][1], $oExcel)
        $oSheet.Cells($i + 1, 2).Value = $dt
    Next

    ; Format column B
    $oSheet.Columns("B:B").NumberFormat = "mm/dd/yy h:mm AM/PM"
    $oSheet.Columns("B:B").HorizontalAlignment = -4131 ; xlLeft

    ; Autofit columns
    $oSheet.Columns("A:B").AutoFit

    ; Apply filter
    $oSheet.Range("A1:B1").AutoFilter

    ; Sort by Start Time ascending
    Local $iLastRow = UBound($aAppointments)


    Local $oRange = $oSheet.Range("A1:B" & $iLastRow)
Local $oKey = $oSheet.Range("B2:B" & $iLastRow) ; actual data range in column B
$oRange.Sort($oKey, 1, Default, Default, Default, Default, Default, 1) ; xlYes=1 for headers


    MsgBox(64, "Export Complete", "Appointments written to Excel.")

   $oSheet = 0
    $oWorkbook = 0
$oRange = 0
$oKey= 0
EndFunc

Func ConvertDateStringToExcelSerial($sDateTime, $oExcel)
    If StringLen($sDateTime) < 14 Then Return ""

    Local $year = StringMid($sDateTime, 1, 4)
    Local $month = StringMid($sDateTime, 5, 2)
    Local $day = StringMid($sDateTime, 7, 2)
    Local $hour = StringMid($sDateTime, 9, 2)
    Local $min = StringMid($sDateTime, 11, 2)
    Local $sec = StringMid($sDateTime, 13, 2)

    ; Build a full datetime string
    Local $fullDateTime = $month & "/" & $day & "/" & $year & " " & $hour & ":" & $min & ":" & $sec

    ; Use Excel's Evaluate to parse it as a date
    Local $serial = $oExcel.Evaluate("DATEVALUE(""" & $month & "/" & $day & "/" & $year & """) + TIMEVALUE(""" & $hour & ":" & $min & ":" & $sec & """)")

    Return $serial
EndFunc







#include <TrayConstants.au3>


Opt("TrayMenuMode", 1) ; Default tray menu items are hidden

; Create tray menu items
Local $idExport = TrayCreateItem("Export Calendar")
Local $idExit = TrayCreateItem("Exit")

TraySetState($TRAY_ICONSTATE_SHOW) ; Show the tray icon

While True
    Switch TrayGetMsg()
        Case $idExport
            Cal_List()
        Case $idExit
            Exit
    EndSwitch
WEnd

 

[water]

That is because your script still holds a reference to the Excel application until you call _Excel_Close.  

[HighlanderSword]

If I put in the _Excel_Close , it closed the Excel doc before user has closed it

[Nine]

Maybe this ?

#include <Excel.au3>

Local $oErr = ObjEvent('AutoIt.Error', COMError)

Local $oExcel = _Excel_Open(True, True)
Local $oWork = _Excel_BookNew($oExcel, 1)
Local $oSheet = $oWork.Sheets(1)

; Headers
$oSheet.Cells(1, 1).Value = "Subject"
$oSheet.Cells(1, 2).Value = "Start Time"

Local $bSaved

While Sleep(100)
  $bSaved = $oWork.saved
  If @error Then ExitLoop
WEnd

Func COMError($oError)
EndFunc

The idea is to test anything about the workbook.  If the workbook is closed (by the user), it generates a COM error that you can exit on...

Edited July 24 by Nine

[water]

You could grab the event sent by Excel before a workbook gets closed. Something like this:

#include <Excel.au3>

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 $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)

ObjEvent($oWorkbook, "Workbook_")
While 1
    Sleep(10)
WEnd
Exit

Func Workbook_BeforeClose()
    MsgBox(0, "WorkbookBeforeClose", "Name: " & $oWorkbook.Name)
    _Excel_Close($oExcel)
    _Exit()
EndFunc   ;==>Workbook_BeforeClose

Func _Exit()
    Exit
EndFunc   ;==>_Exit

This example works for the workbook opened by this script.
If the user opens and closes another workbook nothing will happen.

Edited July 24 by water