_Excel_Close not working?

Damein · April 25, 2016

So I've got a program that is compiling a bunch of data into an excel sheet but I've ran into a snag.

Scenario A:

Run Search > Find No Data > MsgBox States No Data > Closes Excel Properly

Run Search > Find Data > MsgBox States Data Found > Inputs Data Into Excel (Manual Close Excel)

Run Search > Find No Data > MsgBox States No Data > Doesn't Close Excel

End Scenario A

Scenario B:

Run Search > Find No Data > MsgBox States No Data > Closes Excel Properly

Run Search > Find Data > MsgBox States Data Found > Inputs Data Into Excel (Manual Close Excel)

Run Search > Find No Data > MsgBox States No Data > Doesn't Close Excel

End Scenario B

So something is messing it up when I manually close excel but I can't seem to figure out what.

This is also a first draft so I'll fine tune some things later I imagine but its working how I want it to currently.

This is a video showing scenario A.

Also, in order to properly test it ect. you'll need a proper folder structure. Here is a ZIP containing such.

https://www.dropbox.com/s/zdtrnwjqsmr6bfu/Folder%20Structure.zip?dl=0

And then lastly if its something just simple you can see here is the source.

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Add_Constants=n
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <Excel.au3>
#include <Array.au3>
#include <GUIConstantsEx.au3>
#include <File.au3>
#include <EditConstants.au3>
#include ".\Skins\Axis.au3"
#include "_UskinLibrary.au3"

_Uskin_LoadDLL()
_USkin_Init(_Axis(True))

Opt("GUIOnEventMode", 1)

Global $Count = 0, $Status = 0, $Found = 0
Global $InputDate, $InputID, $Gui

_CreateGUI()

Func _CreateGUI()
    $Gui = GUICreate("Waddell Power - Log Compiler", 400, 600)
    GUISetOnEvent($GUI_EVENT_CLOSE, "_Exit")

    GUICtrlCreateLabel("Employee Search", 115, 100, 200, 40)
    GUICtrlSetFont(-1, 16)

    $InputID = GUICtrlCreateInput("", 20, 140, 360, 30, $ES_CENTER)
    GUICtrlSetFont(-1, 16)

    $EmployeeSearchButton = GUICtrlCreateButton("Search", 110, 200, 180, 60)
    GUICtrlSetOnEvent(-1, "_CompileEmployeeData")
    GUICtrlSetFont(-1, 18)

    GUICtrlCreateLabel("Date Search", 140, 320, 200, 40)
    GUICtrlSetFont(-1, 16)

    $InputDate = GUICtrlCreateInput("", 20, 360, 360, 30, $ES_CENTER)
    GUICtrlSetFont(-1, 16)

    $DateSearchButton = GUICtrlCreateButton("Search", 110, 400, 180, 60)
    GUICtrlSetOnEvent(-1, "_CompileEntireDate")
    GUICtrlSetFont(-1, 18)

    $ClearLogsButton = GUICtrlCreateButton("Clear Logs", 90, 520, 220, 60)
    GUICtrlSetOnEvent(-1, "_ClearTempLogs")
    GUICtrlSetFont(-1, 18)
    GUISetState()
EndFunc   ;==>_CreateGUI

Func _ClearTempLogs()
    GUISetState(@SW_DISABLE, $Gui)
    $CheckClear = MsgBox(4, "Clear Logs", "Are you sure you wish to clear the current logs?")
    If $CheckClear = 6 Then
        DirRemove(@ScriptDir & "/Temp Log Files", 1)
        Sleep(100)
        DirCreate(@ScriptDir & "/Temp Log Files")
        MsgBox(0, "Clear Logs", "Logs have been cleared!")
    Else
        MsgBox(0, "Clear Logs", "Cancelling log clearing!")
    EndIf
    GUISetState(@SW_ENABLE, $Gui)
    Sleep(100)
    WinActivate($Gui)
EndFunc   ;==>_ClearTempLogs


Func _CompileEmployeeData()
    $EmployeeSearch = GUICtrlRead($InputID)
    If $EmployeeSearch = "" Then
        MsgBox(48, "Error", "No employee ID was inputted, cancelling employee search!")
    Else
        GUISetState(@SW_DISABLE, $Gui)
        $Status = 0
        $Found = 0
        FileCopy(@ScriptDir & "/Data/Log Template.xlsx", @ScriptDir & "/Temp Log Files/" & $EmployeeSearch & ".xlsx")

        Local $oExcel = _Excel_Open()
        Local $sWorkbook = @ScriptDir & "/Temp Log Files/" & $EmployeeSearch & ".xlsx"
        Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

        WinWait($EmployeeSearch & ".xlsx")

        $GetEmployeeFolderCount = _FileListToArray(@ScriptDir & "/Data/" & $EmployeeSearch, "*", 2)
        If @error Then
            MsgBox(48, "Error", "Employee ID not found!")
            _Excel_Close($oExcel)
            $Status = 1
        EndIf
        If $Status = 0 Then
            For $a = 1 To $GetEmployeeFolderCount[0]
                $GetLogs = _FileListToArray(@ScriptDir & "/Data/" & $EmployeeSearch & "/" & $GetEmployeeFolderCount[$a], "*", 1)
                If @error Then
                    $Status = 1
                EndIf
                If $Status = 0 Then
                    For $b = 1 To $GetLogs[0]
                        If @error Then
                            ExitLoop
                        EndIf
                        $Found = 1
                        $CurrentLogFolder = @ScriptDir & "/Data/" & $EmployeeSearch & "/" & $GetEmployeeFolderCount[$a] & "/" & $GetLogs[$b]
                        $GetID = IniRead($CurrentLogFolder, "ID", 1, "NA")
                        $GetAction = IniRead($CurrentLogFolder, "Data", "Action", "NA")
                        $GetWorkNumber = IniRead($CurrentLogFolder, "Data", "WorkNumber", "NA")
                        $GetCompanyName = IniRead($CurrentLogFolder, "Data", "CompanyName", "NA")
                        $GetJobInfo = IniRead($CurrentLogFolder, "Data", "JobInfo", "NA")
                        $GetLocationStamp = IniRead($CurrentLogFolder, "Data", "LocationStamp", "NA")
                        $AdjustTimeStamp1 = $GetLogs[$b]
                        $TrimIni = StringTrimRight($AdjustTimeStamp1, 4)
                        $StringTrim1 = StringTrimLeft($TrimIni, 2)
                        $StringTrim2 = StringTrimRight($TrimIni, 2)
                        $FinalTimeStamp = $StringTrim2 & ":" & $StringTrim1

                        $Count = 0

                        For $c = 2 To 1000
                            $FindLastEntry = _Excel_RangeRead($oWorkbook, $oWorkbook.ActiveSheet, "A" & $c)
                            If $FindLastEntry = "" Then
                                $Count = $c
                                ExitLoop
                            EndIf
                        Next
                        _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $GetID, "A" & $Count)
                        _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $GetAction, "E" & $Count)
                        _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $FinalTimeStamp, "I" & $Count)
                        _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $GetLocationStamp, "M" & $Count)
                        _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $GetCompanyName, "Q" & $Count)
                        _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $GetJobInfo, "U" & $Count)
                    Next
                EndIf
                $Status = 0
            Next
            If $Found = 0 Then
                MsgBox(48, "Error", "No data found for Employee ID: " & $EmployeeSearch)
                _Excel_Close($oExcel)
                GUISetState(@SW_ENABLE, $Gui)
                Sleep(100)
                WinActivate($Gui)
            Else
                MsgBox(0, "Employee Search", "Compliation complete")
                GUISetState(@SW_ENABLE, $Gui)
            EndIf
        Else
            GUISetState(@SW_ENABLE, $Gui)
            Sleep(100)
            WinActivate($Gui)
        EndIf

    EndIf
EndFunc   ;==>_CompileEmployeeData

Func _CompileEntireDate()
    $DateSearch = GUICtrlRead($InputDate)
    If $DateSearch = "" Then
        MsgBox(48, "Error", "No date was inputted, cancelling date search!")
    Else
        $Found = 0
        GUISetState(@SW_DISABLE, $Gui)
        FileCopy(@ScriptDir & "/Data/Log Template.xlsx", @ScriptDir & "/Temp Log Files/" & $DateSearch & ".xlsx")

        Local $oExcel = _Excel_Open()
        Local $sWorkbook = @ScriptDir & "/Temp Log Files/" & $DateSearch & ".xlsx"
        Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

        WinWait($DateSearch & ".xlsx")

        $GetEmployeeFolderCount = _FileListToArray(@ScriptDir & "/Data", "*", 2)
        For $a = 1 To $GetEmployeeFolderCount[0]
            $GetLogs = _FileListToArray(@ScriptDir & "/Data/" & $GetEmployeeFolderCount[$a] & "/" & $DateSearch, "*", 1)
            If @error Then
                ExitLoop
            EndIf
            For $b = 1 To $GetLogs[0]
                If @error Then
                    ExitLoop
                EndIf
                $Found = 1
                $CurrentLogFolder = @ScriptDir & "/Data/" & $GetEmployeeFolderCount[$a] & "/" & $DateSearch & "/" & $GetLogs[$b]
                $GetID = IniRead($CurrentLogFolder, "ID", 1, "NA")
                $GetAction = IniRead($CurrentLogFolder, "Data", "Action", "NA")
                $GetWorkNumber = IniRead($CurrentLogFolder, "Data", "WorkNumber", "NA")
                $GetCompanyName = IniRead($CurrentLogFolder, "Data", "CompanyName", "NA")
                $GetJobInfo = IniRead($CurrentLogFolder, "Data", "JobInfo", "NA")
                $GetLocationStamp = IniRead($CurrentLogFolder, "Data", "LocationStamp", "NA")
                $AdjustTimeStamp1 = $GetLogs[$b]
                $TrimIni = StringTrimRight($AdjustTimeStamp1, 4)
                $StringTrim1 = StringTrimLeft($TrimIni, 2)
                $StringTrim2 = StringTrimRight($TrimIni, 2)
                $FinalTimeStamp = $StringTrim2 & ":" & $StringTrim1

                $Count = 0

                For $c = 2 To 1000
                    $FindLastEntry = _Excel_RangeRead($oWorkbook, $oWorkbook.ActiveSheet, "A" & $c)
                    If $FindLastEntry = "" Then
                        $Count = $c
                        ExitLoop
                    EndIf
                Next
                _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $GetID, "A" & $Count)
                _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $GetAction, "E" & $Count)
                _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $FinalTimeStamp, "I" & $Count)
                _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $GetLocationStamp, "M" & $Count)
                _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $GetCompanyName, "Q" & $Count)
                _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $GetJobInfo, "U" & $Count)
            Next
        Next
        If $Found = 0 Then
            MsgBox(48, "Error", "No data was found!")
            _Excel_Close($oExcel)
            GUISetState(@SW_ENABLE, $Gui)
            Sleep(100)
            WinActivate($Gui)
        Else

            MsgBox(0, "Date Search", "Compliation complete")
            GUISetState(@SW_ENABLE, $Gui)
        EndIf
    EndIf
EndFunc   ;==>_CompileEntireDate

Func _Exit()
    Exit
EndFunc   ;==>_Exit

While 1
    Sleep(10)
WEnd

Thanks for any help!

alien4u · April 25, 2016

Your code is not short and I don't have time right now and this is not a Good answer but:

- Try to make a ConsoleWrite on each If and Else that you have to be sure the Script workflow is working as you think it should.
- Don't make assumptions like: I'm sure is inside this Else now or inside this If now.

You will find the problem easily.

Regards
Alien.

Damein · April 26, 2016

Well, I understand that with a lot of code and something that maybe isn't fresh in your mind you can't know for sure "This is the problem" but with the various scenarios and how I structured the code I knew exactly where the problem area was.

With that being said, I switched the code

_Excel_Close($oExcel)

To

_Excel_Close($oExcel, False, True)

And it did the trick. Now can someone explain to me why this works? I can kind of get that somehow the Excel_Open() did not attach properly so I must force it. But why can I run the search and find no ID's 100x and it always closes the Excel until I run 1x search where it does find data?

Juvigy · April 26, 2016

Because when you manually close the file you only close the Workbook , but not the excel application. And after that the next calls to_Excel_Open() only "attach" to it and don't start a new application. And that is why. From _Excel_Close help - remarks comments:

"If _Excel_Open() connected to an already running instance of Excel then you have to set $bForceClose to True to do the same."

water · April 26, 2016

Exactly what I wanted to post

Sign In

_Excel_Close not working?

Recommended Posts

Damein

Link to comment

Share on other sites

alien4u

Link to comment

Share on other sites

Damein

Link to comment

Share on other sites

Juvigy

Link to comment

Share on other sites

water

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta