1

I am trying to delete a named Worksheet from an .xlsm file.

I followed the example posted here but it is not working for me. When I open the .xlsm file to check whether the Worksheet has been deleted, it is still there.

Here is my code:

$file2 = 'c:\file.xlsm' # destination's fullpath

$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user

$wb2 = $xl.workbooks.open($file2) # open target

$sh2_wb2 = $wb2.sheets | where {$_.name -eq "myWorksheet"}
$sh2_wb2.delete() #Delete original sheet in template

$wb2.close($true) # close and save destination workbook
$xl.quit()
spps -n excel

What am I doing wrong?

Edit:

I changed my code to make the Excel Object visible when opening it. I then noticed that the delete call is being sent, but it is asking the user to confirm whether the delete should happen: Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete.

I then attempted to add a few more displayAlerts = $false in my code, but it is still giving me that prompt.

Here's my updated code, although it still does not work.

$file2 = 'c:\file.xlsm' # destination's fullpath

$xl = new-object -com excel.application -Property @{Visible = $true}
$xl.displayAlerts = $false # don't prompt the user

$wb2 = $xl.workbooks.open($file2) # open target
$wb2.displayAlerts = $false # don't prompt the user

$sh2_wb2 = $wb2.sheets | where {$_.name -eq "myWorksheet"}
$sh2_wb2.displayAlerts = $false # don't prompt the user
$sh2_wb2.delete() #Delete original sheet in template

$wb2.close($true) # close and save destination workbook
$xl.quit()
spps -n excel
sion_corn
  • 3,043
  • 8
  • 39
  • 65
  • Your code works. Do you have the rights to execute? http://stackoverflow.com/questions/4037939/powershell-execution-of-scripts-is-disabled-on-this-system Also, you must have at least 1 visible sheet, so if myWorksheet is the only one visible, you will error. – Richard Morgan Feb 10 '14 at 18:23
  • You set me on the right track - please see my edit. – sion_corn Feb 10 '14 at 18:41
  • From your original code, if there is data on myWorksheet, and I set displayAlerts to $true, the sheet does not delete. If displayAlerts is $false, the sheet deletes without issue. This is Excel 2010 I am testing in. – Richard Morgan Feb 10 '14 at 19:08
  • I am also testing in Excel 2010. My original code was set to `displayAlerts = $false` and it still prompts me to delete the worksheet, even when it does not contain any data. – sion_corn Feb 10 '14 at 19:15
  • Is your file really in the root of C:\? You need elevated rights, blah, blah, to save there. My testing was done in a permissible folder. – Richard Morgan Feb 10 '14 at 19:56
  • No, it's in a personal dir that I have admin rights to. – sion_corn Feb 10 '14 at 20:04
  • Are you trying to delete the last worksheet in the workbook? I tried this with Powershell and the worksheet does not get deleted. However adding a 2nd worksheet, I was able to delete the named worksheet `Sheet1` by using `$del = $xlsWb.Sheets | where {$_.Name -eq 'Sheet1'}` then `$del.delete()` – user4317867 Jan 21 '15 at 23:09
  • Have you found an answer to this question? – MGP Jan 20 '21 at 15:48

1 Answers1

0

Try this, its help for me :

$Excel = New-Object -ComObject Excel.Application
$workbook = $Excel.workbooks.add()
#working with sheets
$workbook.worksheets.item("Sheet1").Delete()
Bogdan Kulish
  • 1