Delete Sheet If Contains

[rabidfly]

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



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


Thanks.
Dan

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg (https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg)
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg (https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9 (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I)
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3 (https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
ttps://www.youtube.com/watch?v=LP9fz2DCMBE (ttps://www.youtube.com/watch?v=LP9fz2DCMBE)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8 (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8)
ttps://www.youtube.com/watch?v=bFxnXH4-L1A (ttps://www.youtube.com/watch?v=bFxnXH4-L1A)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg)
ttps://www.youtube.com/watch?v=GqzeFYWjTxI (ttps://www.youtube.com/watch?v=GqzeFYWjTxI)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[snb]

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]

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



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]

You'd better use:


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]

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).clearconte nts
if err.number=0 then sh.delete
err.clear
next
wb.close true

set wb=nothing