Jump to content
Damein

_Excel_Close not working?

Recommended Posts

Damein

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
alien4u

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
Damein

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
Juvigy

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
water

Exactly what I wanted to post ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

×