Unexpected files created when merging Excel files

[Becca]

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

expandcollapse popup

#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:

expandcollapse popup

#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)

 

[TheXman]

The files and folder that you have described sound similar to the ones that would be created if you saved the spreadsheet as HTML.  Did you ever save the spreadsheet as HTML?

 

[Becca]

Thank you for your reply. No, the spreadsheets do not get saved as html.

[TheXman]

If you delete those files and folder, the files and folder get recreated every time you run your scripts?

[Becca]

Yes, they get created each time the scripts are run.

[TheXman]

Which version of Excel are you using?

[Becca]

The version is Office 365 Pro Plus, Excel Version 1705.

[TheXman]

Can you try changing your SaveAs command to save it as a .xlsx and see if it creates the additional files and folder?

_Excel_BookSaveAs($oWorkBook, $sDirPath & "\" & $aDirList[$i] & " Monthly " & $M & "_" & $sTwoDigitYr & ".xlsx")

 

The SaveAs command, by default, uses $xlWorkbookDefault as its format type.  Below, is how it is defined in the Include file.  I read somewhere that the extension needs to match or weird things happen. So you should be saving as .xlsx if you are taking the default format. 

Global Const $xlWorkbookDefault = 51 ; Workbook default (.xls for < Excel 2007, .xlsx for > Excel 2007)

 

[Becca]

Thank you Xman. I tried this but it is still creating the extra files. I passed in $xlWorkbookDefault as the format (3rd parameter) in addition to setting the extension as ".xlsx". I'm wondering if it has something to do with the _Excel_SheetCopyMove command. Will take a closer look at that one.

[TheXman]

One more thing you can try is changing:

_Excel_BookClose($oReadBook)

To

_Excel_BookClose($oReadBook, False)

 

Since $oReadBook is just being read, there no need to save it when closing it.

Whatever is causing the extra files and folder, it is probably happening when you save the file.  The only files that you are saving over and over is the $oReadBook due to the default for _Excel_BookClose being to save the file.

It's worth a shot. 

[Becca]

Thank you. I've been tinkering with $oReadBook vs $oWorkBook. I'll try your suggestion. Thanks again!

[Becca]

It looks like that fixed it XMan!! (I.e., adding 'False' to the close command for $oReadBook). Going to do some more testing with a larger set of files but looking good in small-scale testing. Thanks again!