Search the Community

With the help of this forum I have successfully created two autoit scripts that march through a series of folders and merge the Excel files in each folder into one Excel workbook. One script is for invoices and one is for monthly reports. Two problems: (1) When I run the invoice script, in addition to the merged Excel workbooks I also get a text file created in 'Documents' and 'My Documents' for each tab of the invoice. (2) When I run the monthly report script, I get the same unexpected text file creation plus a folder is created for each original directory. Inside each folder are the following files: "filelist.xml", "sheet001.htm", "stylesheet.css" and "tabstrip.htm". Can anyone help me understand why this is happening and whether I can prevent these files from being created (or clean them up after the merging)? This merging task will eventually be turned over to another person and I don't want them to have to look for/clean up these extra files. (These scripts will be merging hundreds of Excel files on the first of each month.) Any help or advice would be greatly appreciated. The code for both scripts is below. Thank you. Invoices #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.14.3 Author: myName Script Function: Template AutoIt script. #ce ---------------------------------------------------------------------------- ; Script Start - Add your code below here #include <Array.au3> #include <Date.au3> #include <Excel.au3> #include <File.au3> #include <MsgBoxConstants.au3> Local $xlPortrait=2 ;Landscape Local $sDirPath = @DesktopDir & "\MergingExcel_Invoices" Local $sJulianDate = _DateToDayValue(@YEAR, @MON, @MDAY) Local $Y, $M, $D Local $NewTabName Local $GetTabName $sJulianDate = _DayValueToDate($sJulianDate, $Y, $M, $D) $sTwoDigitYr = StringRight($Y,2) ;~ Nav through folders in the designated directory. ; 2 means 'Return folders only' Local $aDirList = _FileListToArray($sDirPath, "*", 2) If @error = 1 Then MsgBox($MB_SYSTEMMODAL, "", "Path was invalid.") Exit EndIf If @error = 4 Then MsgBox($MB_SYSTEMMODAL, "", "No file(s) were found.") Exit EndIf Local $aXlsList, $oWorkBook, $oReadBook, $iWorkSheets = 1 Local $oExcel = _Excel_Open(True, False, True, True, True) ;~ Nav through folders For $i = 1 To $aDirList[0] $aXlsList = _FileListToArray($sDirPath & "\" & $aDirList[$i], "*.xls", 1, True) ;_ArraySort($aXlsList,1,1,0,0,0) If @error = 1 Then MsgBox($MB_SYSTEMMODAL, "", "Path:" & $sDirPath & "\" & $aDirList[$i] & " is invalid.") ContinueLoop EndIf If @error = 4 Then MsgBox($MB_SYSTEMMODAL, "", "No file(s) were found.") ContinueLoop EndIf $oWorkBook = _Excel_BookNew($oExcel,1) ;Create new workbook ;~ Nav through files in each folder For $j = 1 To $aXlsList[0] $oReadBook = _Excel_BookOpen($oExcel, $aXlsList[$j], True) ;Open workbook With $oExcel.ActiveSheet.PageSetup .Orientation=$xlPortrait Endwith ;Count sheets, copy each and move For $k = 1 To $oReadBook.Sheets.Count _Excel_SheetCopyMove($oReadBook, $k, $oWorkBook, Default, True, True) ;Default=First Sheet is target sheet, True=Before target sheet, True=Copy (False is Move) $NewTabName=_Excel_RangeRead($oReadBook,Default,"A1:A1") ;Rename worksheet using value in A1 $oWorkBook.ActiveSheet.Name=$NewTabName ;Remove value from A1 _Excel_RangeWrite($oWorkBook,$oWorkBook.ActiveSheet,"","A1") ;Delete row 1 _Excel_RangeDelete($oWorkBook.ActiveSheet,"1:1") ;Make header row bold $oWorkBook.ActiveSheet.Range("1:1").Font.Bold=True $iWorkSheets += 1 Next ;Autofit columns Local $ColNum=$oReadBook.ActiveSheet.UsedRange.Columns.Count Local $LastCol=_Excel_ColumnToLetter($ColNum) $oExcel.ActiveSheet.Columns("A:"&$LastCol).AutoFit ;Close workbook _Excel_BookClose($oReadBook) Next $iWorkSheets = 1 ;Delete the blank Sheet1 _Excel_SheetDelete($oWorkBook,"Sheet1") ;Name workbook _Excel_BookSaveAs($oWorkBook, $sDirPath & "\" & $aDirList[$i] & " Monthly " & $M & "_" & $sTwoDigitYr & ".xls") ;Close workbook _Excel_BookClose($oWorkBook) Next _Excel_Close($oExcel) Monthly reports: #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.14.3 Author: myName Script Function: Template AutoIt script. #ce ---------------------------------------------------------------------------- ; Script Start - Add your code below here #include <Array.au3> #include <Date.au3> #include <Excel.au3> #include <File.au3> #include <MsgBoxConstants.au3> Local $xlPortrait=2 ;Landscape Local $sDirPath = @DesktopDir & "\MergingExcel_Reports" Local $sJulianDate = _DateToDayValue(@YEAR, @MON, @MDAY) Local $Y, $M, $D Local $NewTabName Local $GetTabName Local $LastRowNum Local $GetMod $sJulianDate = _DayValueToDate($sJulianDate, $Y, $M, $D) $sTwoDigitYr = StringRight($Y,2) ;~ Nav through folders in the designated directory. ; 2 means 'Return folders only' Local $aDirList = _FileListToArray($sDirPath, "*", 2) If @error = 1 Then MsgBox($MB_SYSTEMMODAL, "", "Path was invalid.") Exit EndIf If @error = 4 Then MsgBox($MB_SYSTEMMODAL, "", "No file(s) were found.") Exit EndIf Local $aXlsList, $oWorkBook, $oReadBook, $iWorkSheets = 1 Local $oExcel = _Excel_Open(True, False, True, True, True) ;~ Nav through folders For $i = 1 To $aDirList[0] $aXlsList = _FileListToArray($sDirPath & "\" & $aDirList[$i], "*.xls", 1, True) _ArraySort($aXlsList,1,1,0,0,0) If @error = 1 Then MsgBox($MB_SYSTEMMODAL, "", "Path:" & $sDirPath & "\" & $aDirList[$i] & " is invalid.") ContinueLoop EndIf If @error = 4 Then MsgBox($MB_SYSTEMMODAL, "", "No file(s) were found.") ContinueLoop EndIf $oWorkBook = _Excel_BookNew($oExcel,1) ;Create new workbook ;~ Nav through files in each folder For $j = 1 To $aXlsList[0] $oReadBook = _Excel_BookOpen($oExcel, $aXlsList[$j], True) ;Open workbook With $oExcel.ActiveSheet.PageSetup .Orientation=$xlPortrait Endwith ;Count sheets, copy each and move For $k = 1 To $oReadBook.Sheets.Count _Excel_SheetCopyMove($oReadBook, $k, $oWorkBook, $iWorkSheets, False, True);$iWorkSheets=target sheet, False=After target sheet, True=Copy (False is Move) $NewTabName=_Excel_RangeRead($oReadBook,Default,"A1:A1") ;Rename worksheet using value in A1 $oWorkBook.ActiveSheet.Name=$NewTabName ;Remove value from A1 _Excel_RangeWrite($oWorkBook,$oWorkBook.ActiveSheet,"","A1") ;Insert formulas $GetMod=Mod($iWorkSheets,2) If $GetMod=0 Then ;insert formulas for AP sheet $LastRowNum=$oReadBook.ActiveSheet.UsedRange.Rows.Count _Excel_RangeWrite($oWorkBook,$oWorkBook.ActiveSheet, "=IF(D13=0,0.00%,(E13/D13))", "F13:F"&$LastRowNum, False) _Excel_RangeWrite($oWorkBook,$oWorkBook.ActiveSheet, "=IF(D13=0,0.00%,(I13/D13))", "J13:J"&$LastRowNum, False) _Excel_RangeWrite($oWorkBook,$oWorkBook.ActiveSheet, "=IF(D11=0,0.00%,(E11/D11))", "F11:F11", False) _Excel_RangeWrite($oWorkBook,$oWorkBook.ActiveSheet, "=IF(D11=0,0.00%,(I11/D11))", "J11:J11", False) Else ;insert formula for non-AP sheet _Excel_RangeDelete($oWorkBook.ActiveSheet,"I:I",2) EndIf $iWorkSheets += 1 Next ;Autofit columns Local $ColNum=$oReadBook.ActiveSheet.UsedRange.Columns.Count Local $LastCol=_Excel_ColumnToLetter($ColNum) $oExcel.ActiveSheet.Columns("A:"&$LastCol).AutoFit ;Close workbook _Excel_BookClose($oReadBook) Next $iWorkSheets = 1 ;Delete the blank Sheet1 _Excel_SheetDelete($oWorkBook,"Sheet1") ;Name workbook _Excel_BookSaveAs($oWorkBook, $sDirPath & "\" & $aDirList[$i] & " Monthly " & $M & "_" & $sTwoDigitYr & ".xls") ;Close workbook _Excel_BookClose($oWorkBook) Next _Excel_Close($oExcel)