GUI Close Button Not working...

[jfcby]

Hello,

With the following code when I start my program the close button works until I click the Insert Button at line 99 then it stop working. What is causing the close button to stop working?

expandcollapse popup

#include <Word.au3>
#include <Math.au3>
#include <ExcelCOM_UDF.au3>
#include <GUIConstantsEx.au3>

Dim $sObjName, $fDisplayAlerts, $fScreenUpdating
Dim $btn_FF, $btn_EquipListFF, $btn_Insert, $btn_Next, $btn_Previous, $msg
Dim $ffSignin, $fPath1, $fPath2, $fpath3, $sFilePath1, $sFilePath2
Dim $nrow, $lrow, $startcell, $lastcell 
Dim $filemenu, $exititem, $btn_Exit

$ffSignin = "http://ff.ppd.uga.edu/Citrix/AccessPlatform/auth/login.aspx"
$fPath1 = @HomeDrive & "\Documents and Settings\All Users\Documents\FFPM\"
$fPath2 = "Equip_List_FF.xls"
$fpath3 = "Equip_List_FF.exe"
$wbkEquipListFF = $fPath1 & $fpath2
$sFilePath1 = $fPath1 & $fpath2;@ScriptDir & "\Test1.xlsx"
$sFilePath2 = "";@ScriptDir & "\Test2.xlsx"
 
 GUICreate("Data AppInserter", 350, 425); will create a dialog box that when displayed is centered  
 HotKeySet("{ESC}", "_Quit") 
 ;Opt("GUICoordMode", 2)
 
 ;Main Label Control
  $SS_CENTER = "0x01"
 ;Main Label Title
  $fBold = "Arial Bold"
  GUISetFont(9, 400, 4, $fBold)  
  GUICtrlCreateLabel("Enter Data Into Facility Focus(FF) Form Excel File.", 5, 10, 345, 15, $SS_CENTER)
  
 ;Main Label Description
  $fCourier_New = "Courier New"
  GUISetFont(9, 400, 1, $fCourier_New)  
  GUICtrlCreateLabel("To enter data into FF PM Module Inventory Managemant from an Excel File Equip_List_FF.xls follow the steps below.", 5, 50, 345, 75, $SS_CENTER )
  
 ;FF Signin
  $fArial = "Arial"
  GUISetFont(9, 400, 1, $fArial)
 ;GuiCtrlCreateGroup("STEP 1", 2, 115, 345, 30)
  GUICtrlCreateLabel("STEP 1: Signin To Facility Focus", 5, 125, 200, 25)
  $btn_FF = GUICtrlCreateButton("Signin", 235, 125, 75, 25);Left, Down, Width, Height
 ;Open Equip_List_FF.xls
  GUICtrlCreateLabel("STEP 2: Open Equip_List_FF.xls File", 5, 155, 215, 25)
  $btn_EquipListFF = GUICtrlCreateButton("Open", 235, 155, 75, 25)
 ;Insert Data into FF
  GUICtrlCreateLabel("STEP 3: Insert Data into FF", 5, 185, 160, 25)  
  $btn_Insert = GUICtrlCreateButton("Insert", 235, 185, 75, 25) 
    
  GUISetState()  ; will display an  dialog box with 3 button
  
 ; Run the GUI until the dialog is closed
  Opt('MustDeclareVars', 1)
  
  While 1
  $msg = GUIGetMsg()  
  Select
   Case $msg = $GUI_EVENT_CLOSE
    ExitLoop
   Case $msg = $btn_FF   
    ; Run/Open FF Signin Webpage
     ShellExecute($ffSignin)
     
     #cs -----Will be added as a feature update in next revision----------
    ; Activate/Set focus to FF Inventory Management Window
            winFInv()
     
     Send("{ALT}")
     Send("{RIGHT 2}")
     Send("{DOWN 2}")
     Send("{Right}")
     Send("{ENTER}")
     MouseClick("left", 134, 65)
     
     Sleep(2000)
     
    ;Activate the Data Inserter Application Window
     winDataInserter()
     #ce ------------------------------------------------------------------------
     
   Case $msg = $btn_EquipListFF
   ;Run/Open Microsoft Excel Workbook: Equip_List_FF.xls    
    ShellExecute($wbkEquipListFF)
    
   Case $msg = $btn_Insert  
;MsgBox(0, 'Insert', 'Inserting Data')  
   ;Check if Excel Workbook Equip_List_FF.xls is open
    _WordErrorHandlerRegister()
    Dim $oExcel1 = _ExcelBookAttach($sFilePath1, "FilePath")
    Dim $oExcel2 = _ExcelBookAttach($sFilePath2, "FilePath")
    
   Sleep(2000)

  ;Set columns & rows that do not change  
   Dim $startcell, $lastcell, $nrow, $lrow, $nrowM, $btn_SetRows, $lrowM, $st1, $msg2   
   ;http://www.autoitscript.com/forum/index.php?showtopic=62427&hl=_ExcelSheetUsedRangeGet
   ;Start Row
    $startcell = "4"
   ;Last Row with data
    $lastcell = $oExcel1.Application.Selection.SpecialCells($xlCellTypeLastCell).Row;Address    
   ;MsgBox(0, "","Last Cell With Data: " & $lastcell & " -- Start Row: " & $startcell)
    
  #cs - Loop through Options:
        option 1. Change start and last rows
        option 2. Loop through Excel_List_FF.xls ActiveWorksheet and insert data in FF
        option 3. Continue with another worksheet or exit
       #ce
   For $opt = 1 to 3    
    If $opt = 1  Then    
    ;Inputboxes to change start  and last row
     GUICtrlCreateLabel("Change Start Row", 25, 225, 100, 25)
     $nrow = GuiCtrlCreateInput($startcell, 135, 225, 75, 25)
     GUICtrlCreateLabel("Change Last Row", 25, 260, 100, 25)
     $lrow = GuiCtrlCreateInput($lastcell, 135, 260, 75, 25) 
     $btn_SetRows = GUICtrlCreateButton("Set Rows", 235, 260, 75, 25);Left, Down, Width, Height
     
     Do
     $msg2 = GUIGetMsg()
     Select
      Case $msg2 = $btn_SetRows  
       $nrowM = GUICtrlRead($nrow)
       $lrowM = GUICtrlRead($lrow)
      ;MsgBox(0, "", "$nrowM = " & $nrowM & " - " & "$lrowM = " & $lrowM) 
      EndSelect
     Until $msg2 = $btn_SetRows  
    Elseif $opt = 2 Then
     Dim $sCellValue1, $sCellValue2, $sCellValue3, $sCellValue4, $sCellValue6, $sCellValue7
     Dim $msg3
    ;Insert Next Part   
     GUICtrlCreateLabel("Insert Next Part", 100, 315, 125, 25)    
     $btn_Next = GUICtrlCreateButton("Next", 235, 315, 75, 25);Left, Down, Width, Height
     GUICtrlCreateLabel("ReInsert Previous Part", 100, 350, 125, 25)      
     $btn_Previous = GUICtrlCreateButton("Previous", 235, 350, 75, 25)   
    ;MsgBox(0, "", "$nrowM = " & $nrowM & " - " & "$lrowM = " & $lrowM) 
    ;MsgBox(0, "", "The Cell Value is: " &  @CRLF & $sCellValue1);& @CRLF & $sCellValue5 & @CRLF & $sCellValue6, )
     $sCellValue1 = StringTrimLeft(_ExcelReadCell($oExcel1, 2, 3), 7);bldgno
     For $i = $nrowM To $lrowM;Loop       
     $sCellValue2 = _ExcelReadCell($oExcel1, $i, 2);serialno 
     $sCellValue3 = _ExcelReadCell($oExcel1, $i, 3);ffinvpart & Equipment
     $sCellValue4 = _ExcelReadCell($oExcel1, $i, 4);description 
     $sCellValue6 = _ExcelReadCell($oExcel1, $i, 6);roomno
     $sCellValue7 = _ExcelReadCell($oExcel1, $i, 7);extradesc or notes
     Do
     $msg3 = GUIGetMsg()
     Select
      Case $msg3 = $btn_Next or $msg3 = $btn_Previous     
      
      ;Call Function to Activate/maximize FF Inventory Management - \\Remote window
       winFFInv()
        
       ; Click in FF Equipment Field  
        MouseClick("left", 114, 135)
       ; Now that the Notepad window is active type some special characters 
        Send($sCellValue3 & "{ENTER}");invpart & Equipment      
        Sleep(2000)
        Send($sCellValue1 & "-" & $sCellValue2 & "{ENTER 2}");serialno      
        Sleep(2000)
        Send($sCellValue4 & "{ENTER 2}");description
        Sleep(2000)
        Send("U" & "{ENTER 7}");status
        Sleep(2000)
        Send("16" & "{ENTER 4}");shop
        Sleep(2000)
        Send($sCellValue1 & "{ENTER}");bldgno
        Sleep(2000)
        Send($sCellValue6);loc
        
        If $sCellValue7 > "" Then
        Sleep(2000)
       ; Click the Extra Description Box 
        MouseClick("left", 516, 210)
       ;Extra Description Box Text
        Send($sCellValue7)
        Sleep(2000)
        Send("!C{ENTER}")   
        Endif 
      
       If $msg3 = $btn_Previous Then 
        $i = $i - 1 
        MsgBox(0, "", "Testing value: " & $i)
       EndIf       
               
      EndSelect   
     Until $msg3 = $btn_Next or $msg3 = $btn_Previous        
     Next
    Else;Option to continue with another worksheet or exit
     GUISetState(@SW_HIDE)
    ;Function to continue or exit
     _ContExt($fpath1, $fpath3)
     Sleep(1000)
    ;Function to quit/exit this program
     _Quit()
    Endif   
   Next  
  EndSelect 
  WEnd

;_ExcelBookClose1($oExcel1, 0)
;Sleep(5000)
;_ExcelBookClose1($oExcel2, 0)

Func _Quit()
     Exit
EndFunc

Func winFFInv()
    ; Activate/Set focus to FF Inventory Management Window
     WinActivate("Inventory Management - \\Remote", "")
    ; Wait for FF Inventory Management Window to become active
     WinWaitActive("Inventory Management - \\Remote")
EndFunc

Func winExcelListFF()
    ;Activate/Set Focus to Excel Worksheet: Equip_List_FF.xls
     WinActivate("Microsoft Excel - Equip_List_FF.xls", "")
    ;Wait for Excel Worksheet: Equip_List_FF.xls to become active
     WinWaitActive("Microsoft Excel - Equip_List_FF.xls", "")
EndFunc 

Func winDataInserter()
    ; Activate/Set focus to FF Inventory Management Window
     WinActivate("Data AppInserter", "")
    ; Wait for Data AppInserter to become active
     WinWaitActive("Data AppInserter")
EndFunc

Func _ContExt($fpath1, $fpath3)
    Local $ContID, $ExitID, $msg

    GUICreate("Continue or Exit", 210, 80)

    GUICtrlCreateLabel("Continue with another worksheet or Exit!", 10, 10)
    $ContID = GUICtrlCreateButton("Continue", 90, 50, 55, 20)   
    $ExitID = GUICtrlCreateButton("Exit", 150, 50, 55, 20)

    GUISetState() ; display the GUI

    Do
        $msg = GUIGetMsg()
        Select
            Case $msg = $ContID
            ; Run/Open FF Signin Webpage
                ShellExecute($fpath1 & $fpath3)
                Exit                
            Case $msg = $ExitID
                Exit
            Case $msg = $GUI_EVENT_CLOSE
                Exit
        EndSelect
    Until $msg = $GUI_EVENT_CLOSE Or $msg = $ExitID
EndFunc  ;==>_ContExit

; #FUNCTION# ====================================================================================================

================
; Name...........: _ExcelBookAttach
; Description ...: Attach to the first existing instance of Microsoft Excel where the search string matches based on the selected mode.
; Syntax.........: _ExcelBookAttach($s_string, $s_mode = "FilePath")
; Parameters ....: $s_string - String to search for
;                 $s_mode   - Optional: specifies search mode:
;                 |FileName - Name of the open workbook
;                 |FilePath - (Default) Full path to the open workbook
;                 |Title    - Title of the Excel window
; Return values .: Success   - Returns an object variable pointing to the Excel.Application, workbook object
;                 Failure   - Returns 0 and sets @ERROR = 1
; Author ........: Bob Anthony (big_daddy)
; Modified.......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......;
; ====================================================================================================

===========================
Func _ExcelBookAttach($s_string, $s_mode = "FilePath")

    Local $o_Result, $o_workbook, $o_workbooks
    
    If $s_mode = "filepath" Then
        $o_Result = ObjGet($s_string)
        If Not @error And IsObj($o_Result) Then
            Return $o_Result
        EndIf
    EndIf

    $o_Result = ObjGet("", "Excel.Application")
    If @error Or Not IsObj($o_Result) Then
        ConsoleWrite("--> Warning from function _ExcelAttach, No existing Excel.Application object" & @CR)
        Return SetError(1, 0, 0)
    EndIf

    $o_workbooks = $o_Result.Application.Workbooks
    If Not IsObj($o_workbooks) Or $o_workbooks.Count = 0 Then
        ConsoleWrite("--> Warning from function _ExcelAttach, No existing Excel.Application windows" & @CR)
        Return SetError(1, 0, 0)
    EndIf

    For $o_workbook In $o_workbooks

        Switch $s_mode
            Case "filename"
                If $o_workbook.Name = $s_string Then
                    Return $o_workbook
                EndIf
            Case "filepath"
                If $o_workbook.FullName = $s_string Then
                    Return $o_workbook
                EndIf
            Case "title"
                If ($o_workbook.Application.Caption) = $s_string Then
                    Return $o_workbook
                EndIf
            Case Else
                ConsoleWrite("--> Error from function _ExcelAttach, Invalid Mode Specified" & @CR)
                Return SetError(1, 0, 0)
        EndSwitch
    Next
    
    ConsoleWrite("--> Warning from function _ExcelAttach, No Match" & @CR)
    Return SetError(1, 0, 0)
EndFunc  ;==>_ExcelBookAttach

;===============================================================================
;
; Description:    Closes the active workbook and removes the specified Excel object.
; Syntax:          _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
; Parameter(s):  $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;              $fSave - Flag for saving the file before closing (0=no save, 1=save) (default = 1)
;              $fAlerts - Flag for disabling/enabling Excel message alerts (0=disable, 1=enable) (default = 0)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;                 @error=1 - Specified object does not exist
;                 @error=2 - File exists, overwrite flag not set
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
;===============================================================================
Func _ExcelBookClose1($oExcel, $fSave = 1, $fAlerts = 0)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    $sObjName = ObjName($oExcel)
    
    If $fSave > 1 Then $fSave = 1
    If $fSave < 0 Then $fSave = 0
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0
    
   ; Save the users specified settings
    $fDisplayAlerts = $oExcel.Application.DisplayAlerts
    $fScreenUpdating = $oExcel.Application.ScreenUpdating
   ; Make necessary changes
    $oExcel.Application.DisplayAlerts = $fAlerts
    $oExcel.Application.ScreenUpdating = $fAlerts
    
    If $fSave Then
        $oExcel.ActiveWorkBook.Save
    EndIf
    
   ; Restore the users specified settings
    $oExcel.Application.DisplayAlerts = $fDisplayAlerts
    $oExcel.Application.ScreenUpdating = $fScreenUpdating
    
    Switch $sObjName
        Case "_Workbook"
           ; Check if multiple workbooks are open
           ; Do not close application if there are
            If $oExcel.Application.Workbooks.Count > 1 Then
                $oExcel.Close
            Else
                $oExcel.Application.Quit
            EndIf
        Case "_Application"
            $oExcel.Quit
        Case Else
            Return SetError(1, 0, 0)
    EndSwitch
    
    Return 1
EndFunc  ;==>_ExcelBookClose1

Thank you for help,
jfcby

[martin]

I don't know but you don't check for $GUI_EVENT_CLOSE with $msg2 or $msg3 so maybe the script is still in one of those loops.

[jfcby]

I don't know but you don't check for $GUI_EVENT_CLOSE with $msg2 or $msg3 so maybe the script is still in one of those loops.

The above is correct. When I click the Insert button it goes into $msg2 loop. If the script is in $msg2 or $msg3 loop how can I get the close button to work in the script?

Thank you for your help,

jfcby

[martin]

The above is correct. When I click the Insert button it goes into $msg2 loop. If the script is in $msg2 or $msg3 loop how can I get the close button to work in the script?

Thank you for your help,

jfcby

Just add a case statement as normal

Do
     $msg2 = GUIGetMsg()
     Select
      Case $GUI_EVENT_CLOSE
            exit
      Case $msg2 = $btn_SetRows 
       $nrowM = GUICtrlRead($nrow)
       $lrowM = GUICtrlRead($lrow)
     ;MsgBox(0, "", "$nrowM = " & $nrowM & " - " & "$lrowM = " & $lrowM)
      EndSelect
     Until $msg2 = $btn_SetRows

Same for $msg3

[jfcby]

Just add a case statement as normal

Do
     $msg2 = GUIGetMsg()
     Select
      Case $GUI_EVENT_CLOSE
            exit
      Case $msg2 = $btn_SetRows 
       $nrowM = GUICtrlRead($nrow)
       $lrowM = GUICtrlRead($lrow)
    ;MsgBox(0, "", "$nrowM = " & $nrowM & " - " & "$lrowM = " & $lrowM)
      EndSelect
     Until $msg2 = $btn_SetRows

Same for $msg3

Thank you, the above modification to the script worked great.

jfcby