Deletion Macro

[MarkAndrews]

Is it possible to have a macro, which could delete the contents in numerous workbooks?

Activated by an IF statement in a seperate workbook

=IF(A1=B1,"Execute","Do Not Execute")

A1 & B1 being dates (A1 '=today()')

 

[lozzablake]

The simple answer is yes. You would need to be a bit more specific on what you want eg

every workbook in a folder or only specific workbooks? You would need to open each workbook, delete the contents then save and close. So if there are a lot of workbooks it could take some time.

delete contents in all sheets, a specific range ?

When is the IF checked? On opening the workbook, when a cell is changed?

 

[MarkAndrews]

Excellent, thankyou

I need to look at this further

Could i ask excel to look at all files in a directory, identify .xls files & delete every single worksheet in all .xls files found, leaving the file names in tact?

I ask this question due to the fact that we create new monthly documents, in a specific DIR each month, the file names are generic & i'm after a bulk way to delete these worksheets, leaving the file names in tact for their replacement

Will XP's registry have a record of the deletion?

Any help with code greatly appreciated

Mark

 

[lozzablake]

Yes, but a workbook must contain at least one sheet. There would be no record of the deletion, but there will be a record somewhere of who last modified the file. Example below. You could re-name the remaining sheet to "Sheet 1" or whatever you want as well. This macro requires a reference to the Microsoft Scripting runtime (Tools->References in the VBE)

Sub SubDeleteAllSheets()
'macro to delete sheets of all workbooks in specified directory
'requires reference set to Microsoft Scripting runtime
    
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim mySheet As Worksheet

        
    'set path for files - change for your folder
    Const myDir As String = "C:\Temp\"
    
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    'loop through each file. check if Excel and if so loop through all sheets and delete (except for one)
    For Each objFile In myFolder.Files
        If Right(objFile.Name, 3) = "xls" Then
            Workbooks.Open myDir & objFile.Name
            'loop through and delete each sheet
            Application.DisplayAlerts = False
            For Each mySheet In Workbooks(objFile.Name).Sheets
                'cannot delete last sheet therefore check for error
                On Error Resume Next
                mySheet.Delete
                On Error GoTo 0
            Next mySheet
            'clear all cells in remaining sheet
            ActiveWorkbook.Sheets(1).Cells.Clear
            Workbooks(objFile.Name).Close savechanges:=True
            
            Application.DisplayAlerts = True
        End If
    Next objFile
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub

 

[MarkAndrews]

ADVERTISEMENT

This macro requires a reference to the Microsoft Scripting runtime (Tools->References in the VBE)

Whats the above?

Thanks for the code BTW

 

[lozzablake]

The code refers to the FileSystemObject. This is an object not found in normal VBA and is part of something called the Microsoft Scripting runtime. In order to use the object you have to set a reference to it and this is done through the Tools->Reference on the VBE. Just click on the correct check box and the reference will be set.

 

[MarkAndrews]

Thanks, as ever

Mark