CODEWITHSUNDEEP
excelpowershellpaste
Tal
Tal

Reputation: 1

Paste into Excel from Powershell

I have tried lots of options to paste information copied from other Excel workbook into my new workbook but not success do that (the range is huge - more them 3000 lines).

Please see sample of my script:

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false
$objExcel.displayAlerts = $false
$Src = [Environment]::GetFolderPath('Desktop')+'\New.xlsx'
$Files = [Environment]::GetFolderPath('Desktop')+'\Org.xlsx'
$wb1 = $objExcel.workbooks.open($Files)
$Worksheetwb1 = $wb1.WorkSheets.item('Org')
$Worksheetwb1.activate()
$Range = $Worksheetwb1.Range('A1:I1').EntireColumn
$Range.Copy() | Out-Null
$wb3 = $objExcel.workbooks.open($Src)
$Worksheetwb3 = $wb3.WorkSheets.item('Dest')
$Worksheetwb3.activate()
$Worksheetwb3.Columns.item('A:I').clear()
$Range3 = $Worksheetwb3.Range('A1:I1').EntireColumn
$Worksheetwb3.Paste($Range.Value2)
$wb3.close($true)
$wb1.close($true)
$objExcel.Quit()

Upvotes: 0

Views: 4693

Answers (3)

Tal
Tal

Reputation: 1

I have found the answer by changing the order of commands the Copy and then immediate after it the paste solved it for me.

Upvotes: 0

G42
G42

Reputation: 10019

Alternatively to Vesper's solution: 

$Worksheetwb3.Range("A1").Paste() | Out-Null

# Paste special (as values)
$Worksheetwb3.Range("A1").PasteSpecial(-4163) | Out-Null

Upvotes: 0

Vesper
Vesper

Reputation: 18757

You're pasting into a wrong range. Worksheet.Paste() has parameters of destination and link, your code uses destination only, which should be a Range belonging to that worksheet. Therefore, the proper line should be this:

$Worksheetwb3.Paste($Range3)

Upvotes: 1

Related Questions