Damein Posted April 25, 2016 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
alien4u Posted April 25, 2016 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.
Damein Posted April 26, 2016 Author 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
Juvigy Posted April 26, 2016 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."
water Posted April 26, 2016 Posted April 26, 2016 Exactly what I wanted to post My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
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