Self-referencing workbook deletion

[Yarroll]

Hello,

Is there a method in VBA for a workbook to delete itself when some
conditions are met? For example, when active sheet contains nothing except
Rows(1) (header).
I have many folders with Excel workbooks, most of them empty, and its really
annoying to manually open them all and check and delete empty ones.

Thanks. Best regards,
Yarroll

 

[Bernie Deitrick]

Yarroll,

Try the macro below, after changing the folder path where indicated. Note
that this macro relies on the activesheet of the workbook being the one you
want to test.

HTH,
Bernie
MS Excel MVP

Sub KillBlankFiles()
With Application.FileSearch
.NewSearch
'Change this to your folder
.LookIn = "C:\Excel"
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
If ActiveSheet.UsedRange.Rows.Count = 1 And _
ActiveSheet.UsedRange.Row = 1 Then
If MsgBox("Do you want to delete " & _
.FoundFiles(i), vbYesNo) = vbYes Then
ActiveWorkbook.Close
Kill .FoundFiles(i)
Else
ActiveWorkbook.Close
End If
Else
ActiveWorkbook.Close
End If
Next i
End If
End With
End Sub

 

[Yarroll]

Thanks Bernie. I didn't realize I can still do something with a workbook
after I 'active-workbook.close' it )
Silly me... Best regards,
Yarroll

Yarroll,

Try the macro below, after changing the folder path where indicated. Note
that this macro relies on the activesheet of the workbook being the one you
want to test.

HTH,
Bernie
MS Excel MVP

Sub KillBlankFiles()
With Application.FileSearch
.NewSearch
'Change this to your folder
.LookIn = "C:\Excel"
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
If ActiveSheet.UsedRange.Rows.Count = 1 And _
ActiveSheet.UsedRange.Row = 1 Then
If MsgBox("Do you want to delete " & _
.FoundFiles(i), vbYesNo) = vbYes Then
ActiveWorkbook.Close
Kill .FoundFiles(i)
Else
ActiveWorkbook.Close
End If
Else
ActiveWorkbook.Close
End If
Next i
End If
End With
End Sub

(snip)