Damein

_Excel_Close not working?

5 posts in this topic

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 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!

 


MCR.jpg?t=1286371579

Most recent sig. I made

Quick Launcher W/ Profiles Topic Movie Database Topic & Website | LiveStreamer Pro Website | YouTube Stand-Alone Playlist Manager: Topic | Weather Desktop Widget: Topic | Flash Memory Game: Topic | Volume Control With Mouse / iTunes Hotkeys: Topic | Weather program: Topic | Paws & Tales radio drama podcast mini-player: Topic | Quick Math Calculations: Topic

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

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?


MCR.jpg?t=1286371579

Most recent sig. I made

Quick Launcher W/ Profiles Topic Movie Database Topic & Website | LiveStreamer Pro Website | YouTube Stand-Alone Playlist Manager: Topic | Weather Desktop Widget: Topic | Flash Memory Game: Topic | Volume Control With Mouse / iTunes Hotkeys: Topic | Weather program: Topic | Paws & Tales radio drama podcast mini-player: Topic | Quick Math Calculations: Topic

Share this post


Link to post
Share on other sites

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."

Share this post


Link to post
Share on other sites

Exactly what I wanted to post ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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