Self-referencing workbook deletion

Y

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
 
B

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
 
Y

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


Bernie Deitrick said:
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
Click to expand...
(snip)
 
Top