none
Need help performing paste special inside powershell RRS feed

  • Question

  • I would like to add a paste special since the $SourceWorkBook contains formula data I do not intend to copy.  Please help me with the correct syntax. 

    $xl=new-object -com excel.application
    $xl.displayAlerts = $false # don't prompt the user
    $xl.visible = $false
    $xl.displayAlerts=$false
    #$xlPasteValues = $wb1.Range('A1','A1')
    $wb1=$xl.workbooks.open($SourceWorkbook, $null, $true)
    $wb2=$xl.workbooks.open($TargetWorkbook)

    $targetRange=$wb2.Worksheets.Item($TargetWorksheet).Range('B3','B3')
    [void]$wb1.Worksheets.Item($SourceWorksheet).Range('C5','C5').copy($targetRange)


    • Edited by NS2012 Sunday, December 6, 2015 6:52 PM subject line
    Sunday, December 6, 2015 6:51 PM

Answers

  • Hi,

    If you only want to past the value instead of the formula, we can use Range.PasteSpecial instead of Range.Copy method.

    Here is an demo for your reference:

    $xl=new-object -com excel.application
     $xl.displayAlerts = $false # don't prompt the user
     $xl.visible = $true
     $xl.displayAlerts=$false
     #$xlPasteValues = $wb1.Range('A1','A1')
     $wb1=$xl.workbooks.open($SourceWorkbook, $null, $true)
     $wb2=$xl.workbooks.open($TargetWorkbook)
    
    $targetRange=$wb2.Worksheets.Item($TargetWorksheet).Range('B3','B3')
     $wb1.Worksheets.Item($SourceWorksheet).Range('C5','C5').copy()
     $wb2.Worksheets.Item($TargetWorksheet).Activate()
     $targetRange.PasteSpecial(-4163)

    You can get more detail about Range.PasteSpecial method from link below:
    Range.PasteSpecial Method (Excel)

    In addition, in this scenario, we also can assign the value directly like targetRange.Value=SourceRange.Value.

    Here are some helpful links about ranges:
    Cells and Ranges

    Cells and Ranges- Concepts

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by André Santo Monday, December 7, 2015 1:30 PM
    • Marked as answer by NS2012 Monday, December 7, 2015 3:05 PM
    Monday, December 7, 2015 3:17 AM
    Moderator

All replies

  • Hi,

    If you only want to past the value instead of the formula, we can use Range.PasteSpecial instead of Range.Copy method.

    Here is an demo for your reference:

    $xl=new-object -com excel.application
     $xl.displayAlerts = $false # don't prompt the user
     $xl.visible = $true
     $xl.displayAlerts=$false
     #$xlPasteValues = $wb1.Range('A1','A1')
     $wb1=$xl.workbooks.open($SourceWorkbook, $null, $true)
     $wb2=$xl.workbooks.open($TargetWorkbook)
    
    $targetRange=$wb2.Worksheets.Item($TargetWorksheet).Range('B3','B3')
     $wb1.Worksheets.Item($SourceWorksheet).Range('C5','C5').copy()
     $wb2.Worksheets.Item($TargetWorksheet).Activate()
     $targetRange.PasteSpecial(-4163)

    You can get more detail about Range.PasteSpecial method from link below:
    Range.PasteSpecial Method (Excel)

    In addition, in this scenario, we also can assign the value directly like targetRange.Value=SourceRange.Value.

    Here are some helpful links about ranges:
    Cells and Ranges

    Cells and Ranges- Concepts

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by André Santo Monday, December 7, 2015 1:30 PM
    • Marked as answer by NS2012 Monday, December 7, 2015 3:05 PM
    Monday, December 7, 2015 3:17 AM
    Moderator
  • Works like a charm.  Thanks for the links as well, very helpful.

    Monday, December 7, 2015 3:06 PM