Copying range from one workbook sheet to another workbook sheet with PowerShell

0
votes

I tried to make a script to copy from one workbook sheet to another and paste in the exact same range. I tried five different solutions but failed yet.

$excel=new-object -comobject excel.application;
$excel.visible=$true;
$SourceWorkBook=$Excel.Workbooks.open("C:\Oldsheet.xlsx");
$TargetWorkBook=$excel.workBooks.open("C:\Newsheet.xlsx");
$SourceWorkBook.WorkSheets.item("Numbers").activate();
$SourceRange=$SourceWorkBook.WorkSheets.item("Numbers").range("B2:G400");
$SourceRange.copy() | out-null;
$TargetWorkBook.worksheets.item("Numbers").paste("B2:G400");

My code above opens the workbooks and selects the range for copy but doesn't paste it itself. In addition I would like to suppress error messages ($Excel.DisplayAlerts = $False didn't work) and save the file after pasting.

1
excelpowershell

1 Answers

1
votes

Try this:

    Add-Type -AssemblyName Microsoft.Office.Interop.Excel

    $excel               = New-Object -ComObject Excel.Application
    $excel.Visible       = $true
    $excel.DisplayAlerts = $false

    $SourceWorkBook=$Excel.Workbooks.Open("C:\Oldsheet.xlsx")
    $TargetWorkbook=$excel.workBooks.Open("C:\Newsheet.xlsx")

    $SourceWorkBook.WorkSheets.Item("Numbers").Activate()
    $SourceRange=$SourceWorkBook.WorkSheets.Item("Numbers").Range("B2:G400")

    [void]$SourceRange.Copy() 
    [void]$TargetWorkBook.worksheets.item("Numbers").Range("B2:G400").PasteSpecial( [Microsoft.Office.Interop.Excel.XlPasteType]::xlPasteAll )

    [void]$SourceWorkBook.Close()
    [void]$TargetWorkBook.Save()
    [void]$TargetWorkBook.Close()

    $excel.DisplayAlerts = $true

    [void]$excel.Quit()

    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null