Delete Sheet If Contains

**rabidfly** · 06-22-2012, 04:31 PM

Using VBS, I would like to search all sheets for "WORD OR PHRASE", if that is found, then delete the WorkSheet it is in.

Code:

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 MATCH SourcePath.xls"
    Wscript.Quit
End If
Set ArgObj = WScript.Arguments
Dim ArgObj, var1, sourceFile
var1 = 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 MATCH and if found, delete the sheet it is in
'for my case, there would always be at least 1 sheet NOT containing MATCH
' - this avoids excel silently ignoring the sheet delete if there is only one sheet
' - to make this more useful for anyone, create an empty sheet before deleting sheet(s) with MATCH
' Dim WS as Worksheet
' Set WS = Sheets.Add
' HELP

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

**snb** · 06-22-2012, 05:43 PM

Code:

Sub snb()
  on error resume next

  with getobject(sourceFile)
    for each sh in .sheets
      sh.cells.find("searchstring",,-4163,1)
      if err.number=0 then sh.delete
      err.clear
    next
    .close true
  end with
end sub

**rabidfly** · 06-23-2012, 04:15 AM

This is close. It is however deleting the first 2 sheets (of which only the first contains MATCH), then leaves the last sheet (which is an empty sheet).

I added your code to mine and I run it like so: cscript //nologo //B xlsDeleteSheetIfContains.vbs C:\test.xls

Code:

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 MATCH 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
'oExcel.Appllication.DisplayAlerts=False 'this is intentionally wrong because it causes excel to exit immediately on failure 

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

'search for MATCH and if found, delete the sheet it is in
'for my case, there would always be at least 1 sheet NOT containing MATCH
' - this avoids excel silently ignoring the sheet delete if there is only one sheet
' - to make this more useful for anyone, create an empty sheet before deleting sheet(s) with MATCH
' Add 1 empty sheet
'oBook.Worksheets.Add
On Error Resume Next
For Each oSheet In oBook.WorkSheets
	Set oRange = oSheet.UsedRange
' Neither Work
'	oRange.Find("searchString")
	oSheet.Cells.Find("searchString")
	If Err.Number=0 Then
		oSheet.Delete
		Err.Clear
	End If

' Does Not Work
'	If oExcel.WorksheetFunction.CountA(oSheet.Cells.Match("searchString")) = 0 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.
Dan

**snb** · 06-23-2012, 01:58 PM

You'd better use:

Code:

Sub snb()
  on error resume next

  with getobject(sourceFile)
    .application.displayalerts=false

    for each sh in .sheets
      sh.cells.find("searchstring",,,1).clearcontents
      if err.number=0 then sh.delete
      err.clear
    next
    .close true
  end with
end sub

You should be aware that a workbook contains at least 1 worksheet.
So if all worksheets contain the string you are lookinng for, the last sheet will not be removed, but the searchstring will in the code I'm suggesting.

**rabidfly** · 06-25-2012, 05:48 AM

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

Code:

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** · 06-25-2012, 01:02 PM

I work in VBA.

Please keep it simple.

Code:

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