CODEWITHSUNDEEP
excelpowershell
Nick Krasnov
Nick Krasnov

Reputation: 27261

Excel copy paste range of cells

How to copy/paste range of cells with preservation of cells' properties' values (row height/width for instance). I do the following:

$excel=new-object -comobject excel.application;
$excel.visible=$true;
$SourceWorkBook=$Excel.Workbooks.open("c:\ret.xls");
$TargetWorkBook=$excel.workBooks.open("c:\test.xlsx");
$SourceWorkBook.WorkSheets.item(1).activate();
$SourceRange=$SourceWorkBook.WorkSheets.item(1).range("A6","P17");
$SourceRange.copy() | out-null;
$TargetWorkBook.worksheets.item(1).paste();

And result is messed up. Rows/columns' height/width differ from their original values.

Upvotes: 2

Views: 19210

Answers (1)

StephenD
StephenD

Reputation: 4122

There is a Range.PasteSpecial which corresponds to what you can do with the keyboard/menus. It allows you to specify what you want copying, see: http://msdn.microsoft.com/en-us/library/office/ff839476 And http://msdn.microsoft.com/en-us/library/office/ff837425

I found some interesting examples (in PowerShell) here: http://theolddogscriptingblog.wordpress.com/2010/06/01/powershell-excel-cookbook-ver-2/

Upvotes: 2

Related Questions