Jump to content

Why does folder close

Recommended Posts


I have an issue with disk space on a server so I wrote a simple little script to check specific directories and save the sizes to an Excel spreadsheet.  For this script, I am still using version  Everything works fine, I just have a question.

#cs ----------------------------------------------------------------------------
    AutoIt Version:
#ce ----------------------------------------------------------------------------
$file = FileOpen(@ScriptDir & "\CMScriptDirList.txt", 0)
If @error Then Exit
;--folder view still OK
$oexcel = _ExcelBookOpen(@ScriptDir & "\CMScriptDirSizesCopy.xlsx")
If @error Then Exit
;--folder view now parent folder
$excelArray = _ExcelReadSheetToArray($oexcel)
$lastrow = $excelArray[0][0]
$lastcol = $excelArray[0][1]
_ExcelWriteCell($oexcel, _NowCalc(), 1, $lastcol + 1)
While 1
    $line = FileReadLine($file)
    If @error Then ExitLoop
    $size = DirGetSize(StringStripWS($line, 3)) / 1024 / 1024
    $iIndex = _ArraySearch($excelArray, $line, 0, 0, 0, 0, 1, 1)
    If @error Then ContinueLoop
    _ExcelWriteCell($oexcel, $size, $iIndex, $lastcol + 1)
_ExcelWriteFormula($oexcel, "=SUM(R2C" & $lastcol + 1 & ":R38C" & $lastcol + 1 & ")", 39, $lastcol + 1)
_ExcelWriteFormula($oexcel, "=R39C" & $lastcol + 1 & "/1024", 40, $lastcol + 1)
$oexcel.ActiveSheet.columns($lastcol).copy ;used to copy the format of the original last column of the spreadsheet
$oexcel.ActiveSheet.columns($lastcol + 1).PasteSpecial(-4122, Default, Default, Default) ;this just pastes the format of the original last column to the new last column
$oexcel.ActiveSheet.Range("A1").Select ;select cell A1 just to unselect the entire column from previous command
$oexcel.columns.AutoFit ;auto sizes the column width
_ExcelBookClose($oexcel, 1) ;save file when closing

The script is compiled and sitting is a sub-directory on the server in question. The text file and the spreadsheet that are used are both in this same folder as well.  When I navigate to the folder and run the script by double-clicking on the executable, the process runs but the folder view where I ran the script will go back up one level so when the script completes, I am in the parent folder from where I started.  I have added message boxes throughout the script and have determined that the folder view goes back up one level at some point after the @error check for the file open and before the @error check for the ExcelBookOpen (where the comments are).

I just wanted to know if someone can tell me why and if there is a way to prevent it. (Note: still using v3.3.8.1 on this machine but slowly converting scripts to v3.3.14.2).



Share this post

Link to post
Share on other sites


Excel may be changing the application or working directory.  You can determine this be displaying the various @....DIR macros during execution.  You can also force a change of the current working directory using the FileChangeDir function.  See the Help file for syntax and a listing of the @....DIR macros.


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

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

  • Similar Content

    • ahha
      By ahha
      I think this is a very basic question, but I'm stumped after trying to solve it for weeks.  The program below illustrates the issue.  I have several instances of Excel open, each instance having several books open, each book with several sheets.  I'm able to list all this information, however I can't seem to figure out the sheet and workbook for a user selected range.  Any hints appreciated because at this point as I feel like a blind squirrel looking for a nut
      #AutoIt3Wrapper_run_debug_mode=Y #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> #include <Debug.au3> ;Illustrate issue I'm having. For a user seletecd range (possibly multiple $oWorkbooks open with multiple sheets), I need to determine the Excel application object of the selected cells and the sheet ;I need $oWorkbook, $WorkSheet, $Range ;Simulate issue - in real world user may have opened Excel and I have no knowledge of the object $oExcel1 = _Excel_Open() ;open first instance _Excel_BookNew($oExcel1) ;workbook with 3 sheets _Excel_BookNew($oExcel1) ;another workbook in same instance with 3 sheets $oExcel2 = _Excel_Open(Default, Default, Default, Default, True) ;open second instance _Excel_BookNew($oExcel2) ;workbook with 3 sheets _Excel_BookNew($oExcel2) ;another workbook in same instance with 3 sheets $oExcel3 = _Excel_Open(Default, Default, Default, Default, True) ;open third instance _Excel_BookNew($oExcel3) ;workbook with 3 sheets _Excel_BookNew($oExcel3) ;another workbook in same instance with 3 sheets ;now here's what I know without a priori knowledge of the objects ;the workbook names are unigue - that is there will never be a Book1 in any but one of the instances or filename (i.e. single open instance of a particular file) $aWorkBooks = _Excel_BookList() ;get an array of all workbooks open ;Success: a two-dimensional zero based array with the following information: ;col 0 - Object of the workbook ;col 1 - Name of the workbook/file ;col 2 - Complete path to the workbook/file If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error listing workbooks.", "@error = '" & @error & "'" & @CRLF &"@extended = '" & @extended & "'") _DebugArrayDisplay($aWorkBooks, "List of all workbooks open. Col 0 = Object, Col 1 = workbook name, Col 2 = full filename path") ;at this point we have the Object associated with the book name but no full filename path as not saved yet ;now list the sheets for each Object Workbook For $i = 0 to UBound($aWorkBooks, $UBOUND_ROWS) - 1 ;0 based $aWorkSheets = _Excel_SheetList($aWorkBooks[$i][0]) ;Col 0 = Workbook object ;Success: a two-dimensional zero based array with the following information: ; 0 - Name of the worksheet ; 1 - Object of the worksheet If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error listing Worksheets.", "@error = '" & @error & "'" & @CRLF & "@extended = '" & @extended & "'") _ArrayDisplay($aWorkSheets, "$aWorkSheets for $aWorkBooks[" & $i & "]") Next MsgBox($MB_SYSTEMMODAL + $MB_OK, "Info", "Select a range in any Excel instance, any Workbook, and any sheet. Then click OK.") ;I have spent weeks trying to figure this out. Looked at Water's UDF (excellent tight code) and got nothing using a default. All need $oExcel ;********** all this is attempts to get it and they all failed ;********** ;from this: https://www.autoitscript.com/autoit3/docs/functions/ObjGet.htm ;found a possible clue in comment "Error Getting an active Excel Object. <------- **ACTIVE** - so try it Local $oDefaultActiveExcelObject = ObjGet("", "Excel.Application") ; Get an existing Excel Object If @error Then MsgBox($MB_SYSTEMMODAL, "DEBUG", "Error Getting an active Excel Object. Error code: " & Hex(@error, 8)) Else MsgBox($MB_SYSTEMMODAL, "DEBUG", "Success - we got an active Excel Object") EndIf ;Now I have the object so get the rest of the info. We could check this instance against the opened ones. ;hard coded for testing. If $oDefaultActiveExcelObject = $oExcel1 Then MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oExcel1 is the active Excel Object") Else If $oDefaultActiveExcelObject = $oExcel2 Then MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oExcel2 is the active Excel Object") Else If $oDefaultActiveExcelObject = $oExcel3 Then MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oExcel3 is the active Excel Object") Else MsgBox($MB_SYSTEMMODAL, "DEBUG", "ERROR - I have no idea what the active Excel Object is.") EndIf EndIf EndIf ;go ahead and get information MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oDefaultActiveExcelObject.ActiveWorkbook.Name = '" & $oDefaultActiveExcelObject.ActiveWorkbook.Name & "'") ; <<<<<<<<<<<<<------------ this picked the wrong one. **So it looks like each instance has an active workbook.** ;At this point I'm really stumped. I probably should submit to the experts. ;I need to find $oExcel, $oWorkbook, $vWorkSheet, for the user selected range because I want to use ;$vRange = _Excel_RangeRead($oWorkbook, $vWorksheet, $oExcel.Selection.Address) ;this returns absolute like $D$3:$E$5 UNLESS it's a single cell then it returns only single absolute like $E$2 $vRange = _Excel_RangeRead("Book4", "Sheet2", "C2") ;this returns absolute like $D$3:$E$5 UNLESS it's a single cell then it returns only single absolute like $E$2 MsgBox(0, "Info", "The name of the active sheet is '" & $oExcel1.ActiveSheet.Name & "'") ;still need application object $oExcel1 MsgBox($MB_SYSTEMMODAL, "Info", "$vRange = '" & $vRange & "'") ;knowing $oExcel instance might be helpful ;$vRange = $oExcel.Selection.Address ;this returns absolute like $D$3:$E$5 UNLESS it's a single cell then it returns only single absolute like $E$2 ;I need to know the $oExcel ;I don't think I can use _Excel_BookAttach in any way as I need to know in advance a string, a filename, or an instance ;Au3Info not showing any distinctions - I'm stuck. MsgBox($MB_SYSTEMMODAL, "Info", "Pause before exit.") Exit  
    • XinYoung
      By XinYoung
      HI! ... this is a big one (at least for me) 
      You guys previously helped me copy the used range in column A and paste them into a Website one at a time in a loop. Cool! Now, for another function, I have 2 columns, A and B, and two input boxes in the Website. I'm having a hard time replicating the loop for the 2 columns. 
      This is how I'm opening the Excel workbook (copied from the previous function that only had 1 column). I need to also get the used range in column B.
      Func OpenExcelForCopy() Global $aBBTableData Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True) $oExcel.Sheets("CopyCourses").Activate ;~ Get all used cells in column A:A Global $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:A")) ;~ Duplicate the $aSearchItems Array Global $aSearchResult = $aSearchItems ;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1) For $i = 0 To UBound($aSearchItems) - 1 $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i]) Next _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Finished() EndFunc ;==>OpenExcelForCopy Then we eventually get here. I don't think anything needs to change here but I'm not sure. This is where I paste the data from Column A into an input field (which is a search tool in a website). If the search is good, then we get to the tricky part...
      ;~ OK, we logged in and we searched for a course. Lets COPY it! Func CopyCourseBegin() Local $sResult $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0) ;~ If the course was not found, do this. If $iSearchIndex = -1 Then ;~ MsgBox(4096, "Search Error", "Item not found") $sResult = "Source Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") ;~ Now go back to the Excel sheet and search for the next one. ;~ If the course was found, begin the COPY process. Else For $i = 0 To UBound($aSearchItems) - 1 $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i]) Next $sResult = "Copied" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") EndIf Return $sResult EndFunc ;==>CopyCourseBegin This is the "tricky part" where I'm confused. I can copy and paste what's in column A just fine, but I can't manage to replicate it for column B. I need to paste whats in Column B into "destinationCourseId"
      ;~ The course search was successful. COPY the course now. Func CopyCourseNow($_sSearchResult) ;~ Navigate to the course copy page. _IENavigate($oIE, $urlBBCourseCopy) ;~ Copy the SOURCE course ID from the Excel sheet ;~ Paste whats copied from column A into the Source Course ID text box Local $oForm = _IEGetObjByName($oIE, "selectCourse") Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId") _IEFormElementSetValue($oSearchString, $_sSearchResult) ;~ Paste whats copied from column B into the Destination Course ID text box ?!?!?!?! Local $oForm = _IEGetObjByName($oIE, "selectCourse") Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId") _IEFormElementSetValue($oSearchString, $_sSearchResult) ;~ Just exit cause im stuck :( _Exit() EndFunc ;==>CopyCourseNow After I paste the data from column A into "sourceCourseId" and column B into "destinationCourseId", I'll make it do some stuff. Then I need it to loop around until the used ranges in column A & B is finished.
      Does the entire code need to change now that there's two columns?
    • AnonymousX
      By AnonymousX
      I'm trying to be able to switch back and forth between multiple excel spreadsheets and I can't seem to get the WinActivate function to work, and bring the desired window the be the active window.
      Could I please get some assistance, I've tried a few things and nothing seems to work quite right. Below is a test case where I'm just trying to make the first excel sheet that was opened become the active window, and testing it by grabbing a cell value off that workbook. The message box produces the correct answer if both files are closed before running but the 2nd test file will appear to be the active window. If the code is run again without closing the excel files, nothing works (file does not appear to be active and message box will not give an answer).
      #include <Excel.au3> Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase ;Open Test1 Excel Workbook local $oExcel = _Excel_open() Local $ofile = @ScriptDir & "\test1.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel,$ofile) ;Open Test2 Excel Workbook local $mExcel = _Excel_open() Local $mfile = @ScriptDir & "\test2.xlsx" Local $mWorkbook = _Excel_BookOpen($mExcel,$mfile) ; This workbook is completely blank WinActivate($oWorkbook); should make Test1 the active window local $read1 = _Excel_RangeRead($oWorkbook,Default,"B2"); Cell B1 in Test1 workbook contains the word Test MsgBox(0,0,$read1);Should returns the word test  
    • nooneclose
      By nooneclose
      I want to check some Excel data against data on a website in Chrome. I use Chrome because the site I use does not function properly in Internet Explorer or Firefox. I know how to do the Excel stuff I just can not figure out how to send to Chrome, let alone check to see if the data matches or not. I am also having trouble finding any help online while searching for Chrome functions for Autoit. I have a Chrome UDF installed but I still can not figure out how to get my code to properly function. (I am not posting code because I am  sure my code isn't right, to begin with)
      As usual, any and all help would be greatly appreciated. 
    • meral40
      By meral40
      #include <Excel.au3> #include <MsgBoxConstants.au3> #include <Array.au3> ; Create application object and open an example workbook Local $var1= "D:\Documents\testbook.xls" Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, $var1) Local $sRead = _Excel_RangeRead($oWorkbook, Default, "Q2") Local $sRead2 = _Excel_RangeRead($oWorkbook, Default, "Q2") $text1= "hello there" $text2= "read me" While 1=1 If $sRead = $text1 Then ;MouseClick Consolewrite($sRead) Elseif $sRead2 = $text2 Then ;MouseClick Consolewrite($sRead2) EndIf sleep(30000);reads field every 30s WEnd Ok I am writing a script in excel that monitors a field that changes every so often then creates an action based on whether it is text1 or text2 I have problem here if I run script it will read the right text but if I go edit the text in excel it still displays the text before the change.
      Thanks for your help.