问题描述:

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)

网友答案:

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
相关阅读:
Top