Delete sheet2 & sheet3 in excel spreadsheet

[arunkw]

I have lot of excel spreadsheet in lot of different folders which has sheet2 and sheet3 as empty, I want to delete these empty sheets using automation.

Since I am novice in AU3 programming I need help.

Steps which I think that will be required in AU3

1. select the directory.

2. scan the directory for excel sheets (recursively as well).

3. Find out if the any sheet is empty.

4. delete sheets if found empty.

5. repeat same for other files.

[JoHanatCent]

Planning is good.

Step one make sure to read the help provided in AutoIT.

Step two:

Include these in your reading and create a script from them:

#include <Excel.au3>
#include <Array.au3>

_ExcelBookOpen
_ExcelSheetActivate
_ExcelReadSheetToArray
_ExcelSheetDelete

If you are still experiencing a hick up post it here.

Edited November 29, 2010 by JoHanatCent

[arunkw]

Thanks JoHanatCent, I would love to learn fishing rather then getting fish, only thing is it will take time for me to chew.

I have written the code to get the folder and reads all the folders and files inside it and then put it in array.

I guess before calling function recursively and storing it in lot of arrays, it makes sense to first open the excel sheets and check for the empty sheet.

Will do that in 1 or 2 days, any suggestion for me?

expandcollapse popup

;Deleting extra sheets in excel workbook

#include <Array.au3>
;~ #include <Excel.au3> I don't have this I will have to download it 
;~ _ExcelBookOpen
;~ _ExcelSheetActivate
;~ _ExcelReadSheetToArray
;~ _ExcelSheetDelete
;1. select the directory.
;1.1  Call up the directory dialog box select a directory where all excel files and save the path in a variable
$var = FileSelectFolder("Choose a folder.", "")
MsgBox(4096,'Test' , $var, 10)
;2. scan the directory for excel sheets (recursively as well).

; This  Melba23's code, thanks I am pasting it here :-)
_DriveList($var & "\", 1)

Func _DriveList($sInitialPath, $sReturnPath = 0)

    Local $asFolderList[2] = [1, $sInitialPath]
    Local $asFileList[1] = [0]
    
;~     ; Add trailing \ if required
;~     If StringRight($sInitialPath, 1) <> "\" Then $sInitialPath = $sInitialPath & "\"
    
    ; Search in listed folders
    While $asFolderList[0] > 0

        ; Set path to search
        $sCurrentPath = $asFolderList[$asFolderList[0]]
        ; Reduce folder array count
        $asFolderList[0] -= 1

        ; Determine return path to add to file name
        Switch $sReturnPath
            ; Case 0 ; Name only
            ; Leave as ""
            Case 1 ; Initial path not included
                $sRetPath = StringReplace($sCurrentPath, $sInitialPath, "")
            Case 2 ; Initial path included
                $sRetPath = $sCurrentPath
        EndSwitch

        ; Get Search handle
        $hSearch = FileFindFirstFile($sCurrentPath & "*.*")

        ; If folder empty move to next in list
        If $hSearch = -1 Then ContinueLoop

        ; Search folder
        While 1
            $sName = FileFindNextFile($hSearch)
            ; Check for end of folder
            If @error Then ExitLoop
            ; Check for subfolder - @extended set in 3.3.1.1 +
            If @extended Then ; Add to folder list
                ; Increase folder list count
                $asFolderList[0] += 1
                ; Double folder list size if too small (fewer ReDim needed)
                If UBound($asFolderList) <= $asFolderList[0] Then ReDim $asFolderList[UBound($asFolderList) * 2]
                ; Add folder name
                $asFolderList[$asFolderList[0]] = $sCurrentPath & $sName & "\"
            Else ; Add to file list
                ; Increase file list count
                $asFileList[0] += 1
                ; Double file list size if too small (fewer ReDim needed)
                If UBound($asFileList) <= $asFileList[0] Then ReDim $asFileList[UBound($asFileList) * 2]
                ; Add file name
                $asFileList[$asFileList[0]] = $sRetPath & $sName
            EndIf
        WEnd

        ; Close current search
        FileClose($hSearch)

    WEnd

    ; Remove any unused return list elements from last ReDim
    ReDim $asFileList[$asFileList[0] + 1]

    ; Display results
    _ArrayDisplay($asFileList,'List') ; Just for demo - will take a long time if you have a lot of files

EndFunc   ;==>_DriveList
; This  Melba23's code, thanks I am pasting here

;3. Find out if the any sheet is empty.
;4. delete sheets if found empty.
;5. repeat same for other files.

[hannes08]

Hi arunkw,

just two small tips:

1. If you can't find the Excel.au3 you probably are using an old AutoIT version. Try an update.

2. I like to use _FileListToArray() instead of FindNExtFile ... Anyway you could use sth like *.xls for search pattern instead of *.* ...

Regards,

Hannes

[MrMitchell]

You'll still need to find the xls files and open them (pass the Excel obj variable to the function) prior to calling the following function:

Func _ExcelDeleteBlankSheets($oExcel)
    Local $aSheetList, $aSheet, $iCells, $iNumSheets
    ;Create an array of the list of sheets in the Excel workbook $oExcel, index 0 being the total number of sheets
    $aSheetList = _ExcelSheetList($oExcel)
    ;If it's an array (function worked) and if the total sheets is greater than 1 (can't delete any sheets if book only has a single sheet)
    If IsArray($aSheetList) And $aSheetList[0] > 1 Then
        ;Start from the last sheet and end at the first
        For $i = $aSheetList[0] To 1 Step -1
            ;Get the number of sheets left
            $iNumSheets = $oExcel.Sheets.Count
            ;If number of sheets left is 1, can't delete it since workbook has to have at least 1 sheet
            ;so don't even bother checking if the last sheet is blank, just return from function
            If $iNumSheets = 1 Then Return 0    
            ;If able to set the sheet named in the array as the active sheet
            If _ExcelSheetActivate($oExcel, $aSheetList[$i]) Then
                ;Count number of cells which contain data(if this is 0 then the sheet is blank)
                $iCells = $oExcel.WorksheetFunction.CountA($oExcel.ActiveSheet.Cells)
                ;If no cells contain data...
                If $iCells = 0 Then
                    ;Delete the sheet
                    If _ExcelSheetDelete($oExcel, $aSheetList[$i]) Then
                        ;Output says sheet deleted,
                        ;comment next out if you don't want to know if a sheet was deleted.
                        ConsoleWrite("Deleted sheet: " & $aSheetList[$i] & @CRLF)
                    Else
                        ;Out says unable to delete the sheet (who knows why),
                        ;comment next line out if you don't want to know what couldn't be deleted
                        ConsoleWrite("Unable to delete sheet: " & $aSheetList[$i] & @CRLF)
                    EndIf
                EndIf
            EndIf
        Next
    EndIf
EndFunc

The above function's only task is to determine what sheets are empty then delete them. Some of this is in the help files, but one thing that takes a little research is the line that determines whether or not a sheet is blank or empty. There are several ways to do it, this is just one. It could use some additional error checks and such but it should get the job done. Close the xls after the function returns.