CODEWITHSUNDEEP
powershell
user7911521
user7911521

Reputation:

How can i idelete sheet in excel file using powershell?

This is my code, below:

# Specify the path to the Excel file and the WorkSheet Name
$FilePath = "C:\Downloads\Portalroom_CW30.xlsx"
$SheetName = "4_docexchange"
# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false
# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)
# Load the WorkSheet 'BuildSpecs'
$WorkSheet = $WorkBook.sheets.item($SheetName)
$WorkBook.close($true)
$objExcel.Quit()

Please help me resolv, Thanks !!

Upvotes: 1

Views: 6610

Answers (1)

Vivek Kumar Singh
Vivek Kumar Singh

Reputation: 3350

You can call the Delete() method on your worksheet in order to do so. But before that you would want to set DisplayAlerts option as $false and would save the $Workbook after you are done deleting. Your code will look like this - 

#Specify the path to the Excel file and the WorkSheet Name
$FilePath = "C:\Downloads\Portalroom_CW30.xlsx"
$SheetName = "4_docexchange"
#Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
#Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false

#Set Display alerts as false
$objExcel.displayalerts = $False

#Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)
#Load the WorkSheet 'BuildSpecs'
$WorkSheet = $WorkBook.sheets.item($SheetName)

#Deleting the worksheet
$WorkSheet.Delete()
#Saving the worksheet
$WorkBook.Save()
$WorkBook.close($true)
$objExcel.Quit()

Upvotes: 5

Related Questions