Damein 19 Posted April 25, 2016 Share Posted 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 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. expandcollapse popup#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! 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 Link to post Share on other sites
alien4u 19 Posted April 25, 2016 Share Posted 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. Link to post Share on other sites
Damein 19 Posted April 26, 2016 Author Share Posted 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? 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 Link to post Share on other sites
Juvigy 50 Posted April 26, 2016 Share Posted 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." Link to post Share on other sites
water 2,531 Posted April 26, 2016 Share Posted April 26, 2016 Exactly what I wanted to post My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2021-11-10 - Version 1.6.0.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (NEW 2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to post Share on other sites
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now