locked
Copy and Paste in Excel using powershell RRS feed

  • Question

  • Hi,

    I have two excel file such as excel1.xls and excel2.xls. Now I am looking for a powershell script which will copy the contents in the cells A1 to A12 of excel1.xls and paste it to B2 to B13 cells of excel2.xls file.

    Please help.

    Wednesday, February 20, 2013 4:27 PM

Answers

  • $range1="A1:A12"
    $range2="B2:B13"
    $file1 = 'D:\1.xls' 
    $file2 = 'D:\2.xls' 
    $xl = new-object -c excel.application
    $xl.displayAlerts = $false 
    $wb1 = $xl.workbooks.open($file1, $null, $true) 
    $wb2 = $xl.workbooks.open($file2)
    $ws1 = $wb1.WorkSheets.item(1) 
    $ws1.activate()  
    $range = $ws1.Range($range1).Copy()
    
    $ws2 = $wb2.Worksheets.item(1) 
    $ws2.activate()
    $x=$ws2.Range($range2).Select()
    
    $ws2.Paste()  
    $wb2.Save() 
    
    $wb1.close($false) 
    $wb2.close($true) 
    $xl.quit()
    spps -n excel

    • Proposed as answer by Yan Li_ Thursday, February 21, 2013 2:18 AM
    • Marked as answer by Yan Li_ Tuesday, February 26, 2013 5:01 AM
    Wednesday, February 20, 2013 5:05 PM

All replies

  • Change the location of the excel files... in $sourceworkbook and $targetworkbook

    ===========

    $excel=new-object -comobject excel.application;
    $excel.visible=$true;
    $SourceWorkBook=$Excel.Workbooks.open("f:\azam\book1.xls");
    $TargetWorkBook=$excel.workBooks.open("f:\azam\book2.xls");
    $SourceWorkBook.WorkSheets.item(1).activate();
    $SourceRange=$SourceWorkBook.WorkSheets.item(1).range("A1","A12");
    $SourceRange.copy() | out-null;
    $TargetWorkBook.worksheets.item(1).paste();

    ============


    Thanks Azam When you see answers please Mark as Answer if Helpful..vote as helpful.

    Wednesday, February 20, 2013 4:50 PM
  • $range1="A1:A12"
    $range2="B2:B13"
    $file1 = 'D:\1.xls' 
    $file2 = 'D:\2.xls' 
    $xl = new-object -c excel.application
    $xl.displayAlerts = $false 
    $wb1 = $xl.workbooks.open($file1, $null, $true) 
    $wb2 = $xl.workbooks.open($file2)
    $ws1 = $wb1.WorkSheets.item(1) 
    $ws1.activate()  
    $range = $ws1.Range($range1).Copy()
    
    $ws2 = $wb2.Worksheets.item(1) 
    $ws2.activate()
    $x=$ws2.Range($range2).Select()
    
    $ws2.Paste()  
    $wb2.Save() 
    
    $wb1.close($false) 
    $wb2.close($true) 
    $xl.quit()
    spps -n excel

    • Proposed as answer by Yan Li_ Thursday, February 21, 2013 2:18 AM
    • Marked as answer by Yan Li_ Tuesday, February 26, 2013 5:01 AM
    Wednesday, February 20, 2013 5:05 PM