-
Delete Sheet If Contains
-
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
-
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
-
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.
-
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
-
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
