1

I have a very similar request as the one made in this post but the difference is: I would like to overwrite the contents of an existing worksheet in my destination Excel file.

The code (an exact copy from the linked post; but pasted here for convenience) is below:

$file1 = 'C:\path\file1.xls' # source's fullpath
$file2 = 'C:\path\file2.xls' # destination's fullpath

$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user
$wb2 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
$wb1 = $xl.workbooks.open($file2) # open target
$sh1_wb1 = $wb1.sheets.item('MyWorksheet') # second sheet in destination workbook
$sheetToCopy = $wb2.sheets.item('MyWorksheet') # source sheet to copy

$sheetToCopy.copy($sh1_wb1)  # copy source sheet to destination workbook

$wb2.close($false) # close source workbook w/o saving
$wb1.close($true) # close and save destination workbook
$xl.quit()
spps -n excel

The line that needs to be changed is this:

$sheetToCopy.copy($sh1_wb1) # copy source sheet to destination workbook

Can you please tell me how to rewrite the above so it does not create a new worksheet in the destination excel document? Right now, instead of overwriting MyWorksheet it is creating a copy: MyWorksheet (2)

Community
  • 1
  • 1
sion_corn
  • 3,043
  • 8
  • 39
  • 65

1 Answers1

1

Seems what you need is to rename the target sheet, paste the replacement in, and then delete the target.

$file1 = 'C:\path\file1.xls' # source's fullpath
$file2 = 'C:\path\file2.xls' # destination's fullpath

$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user
$wb2 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
$wb1 = $xl.workbooks.open($file2) # open target
$sh1_wb1 = $wb1.sheets.item('MyWorksheet') # second sheet in destination workbook
$sheetToCopy = $wb2.sheets.item('MyWorksheet') # source sheet to copy

$sh1_wb1.Name = "DeleteMe$(get-date -Format hhmmss)" #Extremely unlikely to be a duplicate name
$sheetToCopy.copy($sh1_wb1)  # copy source sheet to destination workbook
$sh1_wb1.Delete()

$wb2.close($false) # close source workbook w/o saving
$wb1.close($true) # close and save destination workbook
$xl.quit()
spps -n excel
TheMadTechnician
  • 34,906
  • 3
  • 42
  • 56
  • 1
    For some reason, `$sh1_wb1.Delete()` does not successfully delete the renamed worksheet. – sion_corn Feb 10 '14 at 14:32
  • Hm, that code works just fine on my machine copied and pasted into PowerShellISE and run with a couple dummy test spreadsheets placed in C:\Path\. Do you get an error or anything? What version of Excel are you running? What version of PowerShell? – TheMadTechnician Feb 11 '14 at 22:16
  • Yeah, it's strange... Excel 2010 .xlsm file (macro-enabled), and PowerShell 2.0 – sion_corn Feb 12 '14 at 02:26
  • You might want to just step through the code a bit, and set Excel to visible while you go `$xl.visible=$true` to watch what happens. Maybe try issuing the delete command by hand from within PS or something. – TheMadTechnician Feb 12 '14 at 15:53