使用PowerShell将整个工作表复制到另一个工作表 [英] Value-copy whole worksheet to another one with PowerShell

问题描述

已经有一些关于如何复制移动一个WS或复制某些范围的文章，但是我感兴趣的是如何将整个WS作为值复制到另一本工作簿中已经存在的WS中.这是我尝试过的:

There are some posts already about how to copy-move one WS or copy some ranges, but what I am interested in is how to copy whole WS as a values into already existing WS in an other workbook. This is what I tried:

$obj_excel = New-Object -ComObject Excel.Application
$obj_excel.DisplayAlerts = $false # don't prompt the user
# Open source and target files
$wb_source = $obj_excel.Workbooks.Open($SourceFile, $null, $true) # open source, readonly
$wb_target = $obj_excel.Workbooks.Open($TargetFile) # open target
[void]$wb_target.Sheets.Item($TargetSheet).Copy()
[void]$wb_source.Sheets.Item($SourceSheet).PasteSpecial(-4163)
$wb_source.Close($false) # close source workbook w/o saving
$wb_target.Close($true) # close and save destination workbook
$obj_excel.Quit()

它显然不起作用.

推荐答案

如果只想复制内容，则不能使用 Copy() Range 对象(特别是 Cells 集合)的 方法:

If you just want to copy the content you can't use the Copy() method of the Worksheet object. Use the Copy() method of the Range object (specifically the Cells collection):

$wb_source.Sheets.Item($SourceSheet).Cells.Copy()
$wb_target.Sheets.Item($TargetSheet).Cells.PasteSpecial(-4163)

这篇关于使用PowerShell将整个工作表复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！