Excel ‘sheetToCopy’

Whenever I see a post from Kiron
I know it will be good.

This copies the ‘sheetToCopy’ sheet from a source workbook (Book1.xlsx) to a destination workbook (Book2.xlsx).

I’ve commented the steps but if you’d like a better description of the process let me know and I’ll try my best…

$file1 = ‘C:Book1.xlsx’                            # source’s fullpath

$file2 = ‘C:Book2.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

$sh1_wb2 = $wb2.sheets.item(1)    # first sheet in destination workbook

$sheetToCopy = $wb1.sheets.item(‘sheetToCopy’)   # source sheet to copy

$sheetToCopy.copy($sh1_wb2) # copy source sheet to destination workbook

$wb1.close($false)                   # close source workbook w/o saving

$wb2.close($true)                 # close and save destination workbook


spps -n excel



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s