Paste into Excel from Powershell

Question

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()

Can you be more specific about exactly what is not working? Do you know how far it gets successfully? If you have a value in A1 instead of a multi-column range, is the script successful? I deal with PowerShell and Excel frequently but am not able to assist without more detailed information. — G42
– G42, Commented May 25, 2017 at 11:15
The Paste is not working for me "$Worksheetwb3.Paste($Range.Value2)" — Tal
– Tal, Commented May 25, 2017 at 11:43

Vesper · Accepted Answer · 2017-05-25 12:11:15Z

1

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)

answered May 25, 2017 at 12:11

Vesper

18.8k6 gold badges41 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

G42 · Accepted Answer · 2017-05-25 12:19:14Z

0

Alternatively to Vesper's solution:

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

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

answered May 25, 2017 at 12:19

G42

10.1k2 gold badges22 silver badges36 bronze badges

Comments

vvvvv · Accepted Answer · 2022-03-17 10:06:55Z

0

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

edited Mar 17, 2022 at 10:06

vvvvv

32.9k19 gold badges70 silver badges103 bronze badges

answered May 29, 2017 at 13:48

Tal

11 silver badge2 bronze badges

Collectives™ on Stack Overflow

Paste into Excel from Powershell

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related