CODEWITHSUNDEEP
excelpowershellcsvcomexcel-interop
Mike
Mike

Reputation: 33

Powershell paste data into existing sheet

I have a script that takes data from an existing .csv file and copies into an existing workbook (xlsx).

The problem: It always creates a new sheet in the workbook.

I would like to add the data to my current sheet and start at A5 instead of the top. Here is what I have

$source = 'C:\Scripts\monthly.csv' # source's fullpath 
$target = 'C:\Scripts\template.xlsx' # destination's fullpath 
$xl = new-object -com excel.application # creates Excel COM object in powershell
$xl.displayAlerts = $false # don't prompt the user 
$wb2 = $xl.workbooks.open($source, $null, $true) # open source, readonly 
$wb1 = $xl.workbooks.open($target) # open target 
$sh1_wb1 = $wb1.sheets.item('triarqnew') # sheet in destination workbook 
$sheetToCopy = $wb2.sheets.item('monthly') # source sheet to copy 
$sheetToCopy.copy($sh1_wb1) # copy source sheet to destination workbook 
$wb2.close($false) # close source workbook w/o saving 
$wb1.SaveAs("C:\scripts\Report.xlsx") # close and save destination workbook 
$xl.quit()

Upvotes: 3

Views: 1876

Answers (1)

DAXaholic
DAXaholic

Reputation: 35358

Try it like that with Copy() and PasteSpecial()

$source = 'C:\Scripts\monthly.csv' # source's fullpath 
$target = 'C:\Scripts\template.xlsx' # destination's fullpath
$xl = new-object -com excel.application # creates Excel COM object in powershell
$xl.displayAlerts = $false # don't prompt the user 
$wb2 = $xl.workbooks.open($source, $null, $true) # open source, readonly 
$wb1 = $xl.workbooks.open($target) # open target 
$sh1_wb1 = $wb1.sheets.item('triarqnew') # sheet in destination workbook 
$sheetToCopy = $wb2.sheets.item('monthly') # source sheet to copy 

# New code
$sheetToCopy.UsedRange.Copy()
$sh1_wb1.Range("A5").PasteSpecial()

$wb2.close($false) # close source workbook w/o saving 
$wb1.SaveAs("C:\scripts\Report.xlsx") # close and save destination workbook 
$xl.quit()

Upvotes: 1

Related Questions