none
Copy Excel data and import into a separate Excel spreadsheet using powershell - Entire roles being filled with data instead of a single cell RRS feed

  • Question

  •    
            $file1 = 'C:\Users\\Desktop\Copy of WDE_Activity11-11-2015 (002).xlsx' # source's fullpath
            $file2 = 'C:\Users\\Desktop\Encrypted Compliance Report.xlsx' # destination's fullpath

    $xl = new-object -c excel.application
    $xl.displayAlerts = $false # don't prompt the user

    $wb1 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
    $wb2 = $xl.workbooks.open($file2) # open target
    $destination = $wb2.sheets.item('Compliance Report') # sheet in destination workbook
    $source = $wb1.sheets.item('sheet2') # source sheet to copy

    #[void]$destination.UsedRange.Clear() # Clear cells that have data in the destination
    [void]$source.UsedRange.Copy() # Copy range of cells with data in them on source sheet
    [void]$destination.Range("C3").Select() # Set first cell of destination as active cell
    [void]$destination.paste("C3") # Paste data into destination sheet starting at active cell (A1)
    #[void]$destination.Range("R3C3").Select() # Set first cell of destination as active cell, otherwise is has everything selected

    $wb1.close($false) # close source workbook w/o saving
    $wb2.close($true) # close and save destination workbook
    $xl.quit()
    spps -n excel


    • Moved by Bill_Stewart Monday, November 30, 2015 6:02 PM Move to more appropriate forum
    • Edited by NS2012 Monday, November 30, 2015 6:58 PM
    Monday, November 30, 2015 5:11 PM

Answers

  • It takes only two lines to copy the contents of a worksheet to another sheet of workbook:

    $targetRange=$wb2.Worksheets.Item($TargetWorksheet).Range('A1','A1')
    [void]$wb1.Worksheets.Item($SourceWorksheet).UsedRange.Copy($targetRange)

    Here is  complete example:

    function Copy-ExcelSheet{
    	param(
    		$SourceWorkbook = 'c:\scripts\test.xlsx',
    		$SourceWorksheet='Sheet1',
    		$TargetWorkbook = 'c:\scripts\newtest.xlsx',
    		$TargetWorksheet='Sheet1'
    	)
    	
    	$xl=new-object -com excel.application
    	$xl.visible = $true
    	$xl.displayAlerts=$false
    	$wb1=$xl.workbooks.open($SourceWorkbook, $null, $true)
    	$wb2=$xl.workbooks.open($TargetWorkbook)
    
    	$targetRange=$wb2.Worksheets.Item($TargetWorksheet).Range('A1','A1')
    	[void]$wb1.Worksheets.Item($SourceWorksheet).UsedRange.Copy($targetRange)
    
    
    	$wb1.close($false)
    	$wb2.close($true)
    	$xl.quit()
    }


    \_(ツ)_/


    Monday, November 30, 2015 6:45 PM

All replies

  • First start by posting you code in a readable format. What you have posted is unreadable.


    \_(ツ)_/

    Monday, November 30, 2015 6:09 PM
  • It takes only two lines to copy the contents of a worksheet to another sheet of workbook:

    $targetRange=$wb2.Worksheets.Item($TargetWorksheet).Range('A1','A1')
    [void]$wb1.Worksheets.Item($SourceWorksheet).UsedRange.Copy($targetRange)

    Here is  complete example:

    function Copy-ExcelSheet{
    	param(
    		$SourceWorkbook = 'c:\scripts\test.xlsx',
    		$SourceWorksheet='Sheet1',
    		$TargetWorkbook = 'c:\scripts\newtest.xlsx',
    		$TargetWorksheet='Sheet1'
    	)
    	
    	$xl=new-object -com excel.application
    	$xl.visible = $true
    	$xl.displayAlerts=$false
    	$wb1=$xl.workbooks.open($SourceWorkbook, $null, $true)
    	$wb2=$xl.workbooks.open($TargetWorkbook)
    
    	$targetRange=$wb2.Worksheets.Item($TargetWorksheet).Range('A1','A1')
    	[void]$wb1.Worksheets.Item($SourceWorksheet).UsedRange.Copy($targetRange)
    
    
    	$wb1.close($false)
    	$wb2.close($true)
    	$xl.quit()
    }


    \_(ツ)_/


    Monday, November 30, 2015 6:45 PM
  • done
    Monday, November 30, 2015 7:01 PM
  • Will this select the entire source spreadsheet or a specific cell? I'd like to pull a single cell.
    Monday, November 30, 2015 7:03 PM
  • Just change UsedRange to the Range you want:

    [void]$wb1.Worksheets.Item($SourceWorksheet).Range('D3','D3').Copy($targetRange)


    \_(ツ)_/

    Monday, November 30, 2015 7:14 PM
  • Thanks that did the trick..
    Monday, November 30, 2015 7:39 PM
  • Thanks that did the trick..

    You are welcome. Office is pretty easy to navigate once you see how the model works. C# examples are better for using with PowerShell.


    \_(ツ)_/

    Monday, November 30, 2015 7:44 PM