Delete Sheet If Contains

[rabidfly]

We're clearly using different tools to accomplish similar things. Thanks for your help! It helped point me in the right direction.

Here's what worked for me: cscript //nologo //B xlsDeleteSheetIfContains.vbs searchString C:\test.xlsx

If WScript.Arguments.Count < 2 Then
	WScript.Echo "Error! Please specify the MATCH to delete Sheet and the file to edit. Usage: wscript xlsDeleteSheetIfContains.vbs searchString SourcePath.xls"
	Wscript.Quit
End If
Set ArgObj = WScript.Arguments
Dim ArgObj, searchString, sourceFile
searchString = ArgObj(0)
sourceFile = ArgObj(1)

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts=False

Dim oBook
Set oBook = oExcel.Workbooks.Open(sourceFile)

'search for searchString and if found, delete the sheet it is in
' Add 1 empty sheet - just in case all sheets contain searchString - we must leave 1 sheet before we exit
oBook.Worksheets.Add

On Error Resume Next
For Each oSheet In oBook.WorkSheets
	Set oRange = oSheet.UsedRange
	Set oTarget = oRange.Find(searchString)
	If Not oTarget Is Nothing Then
		oSheet.Delete
	End If
	' delete all empty sheets
	If oExcel.WorksheetFunction.CountA(oSheet.Cells) = 0 Then
		oSheet.Delete
	End If
Next

oBook.Save
oBook.Close True
oExcel.Quit
WScript.Quit
set oSheet = Nothing
set oBook = Nothing
set oExcel = Nothing

Thanks again!
Dan

[snb]

I work in VBA.

Please keep it simple.

If WScript.Arguments.Count < 2 Then
WScript.Echo "Error! Please specify the MATCH to delete Sheet and the file to edit. Usage: wscript xlsDeleteSheetIfContains.vbs searchString SourcePath.xls"
Wscript.Quit
End If

  on error resume next

  set wb=getobject(WScript.Arguments(1))
  wb.application.displayalerts=false

    for each sh in wb.sheets
      sh.cells.find(WScript.Arguments(0),,,1).clearcontents
      if err.number=0 then sh.delete
      err.clear
    next
    wb.close true

  set wb=nothing