Delete a worksheet from Excel file via VBScript?

[beckwiga]

All-

Having some trouble. Trying to write a VBScript that will delete a worksheet from an Excel file. Sounds simple enough but I can't get this to work. I figured I would pass the xls name as arg1 and the worksheet name as arg2. Also, I don't want to be prompted since I am going to run this from a batch file. Any help would be MUCH appreciated. Thanks, beckwiga.



Option Explicit

Const ForReading = 1, ForWriting = 2, ForAppending = 8

Dim SourceDir, args, filename, worksheetname
Dim InFile, InSheet, DateFile, FileDate, Text, FileOut, Sheet
Dim objShell, objFolder, objFSO, strFileName, objArgs, objExcel, objWorkbook, objSheet

'On Error Resume Next

SourceDir = "F:\input\"

Set args = WScript.Arguments
filename = args(0)
worksheetname = args(1)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")

InFile = SourceDir & filename
InSheet = worksheetname

Set objWorkbook = objExcel.Workbooks.Open (InFile)

objExcel.DisplayAlerts = False

objExcel.sheets(worksheetname).Delete

objExcel.DisplayAlerts = True


objWorkbook.Close
FileOut.Close

objExcel.Quit

 

[SkipVought]

hi,

Set objWorkbook = objExcel.Workbooks.Open (InFile)

objExcel.DisplayAlerts = False   

[b]objWorkbook[/b].sheets(worksheetname).Delete

objExcel.DisplayAlerts = True

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[beckwiga]

Thanks Skip! Working now with one exception... it deletes the worksheet as expected, however I am getting a prompt in the screen about 'workbook contains links to other data'. Do you know how I can suppress it?

Here is my code now:


Option Explicit

Const ForReading = 1, ForWriting = 2, ForAppending = 8

Dim SourceDir, args, filename, worksheetname
Dim InFile, InSheet, DateFile, FileDate, Text, FileOut, Sheet
Dim objShell, objFolder, objFSO, strFileName, objArgs, objExcel, objWorkbook, objSheet

'On Error Resume Next

SourceDir = "F:\RiverNorth\input\"

Set args = WScript.Arguments
filename = args(0)
worksheetname = args(1)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")

InFile = SourceDir & filename
InSheet = worksheetname

'MsgBox(InFile)
'MsgBox(InSheet)

objExcel.DisplayAlerts = False

Set objWorkbook = objExcel.Workbooks.Open (InFile)

objExcel.DisplayAlerts = False
objExcel.ScreenUpdating = False

objWorkbook.sheets(worksheetname).Select
objWorkbook.sheets(worksheetname).Delete

objWorkbook.Save
objWorkbook.Close

objExcel.DisplayAlerts = True
objExcel.ScreenUpdating = True

objExcel.Quit