CODEWITHSUNDEEP
excelpowershell
Dana Dery
Dana Dery

Reputation: 3

Incorrect formula after copying Excel worksheet from another worksheet through powershell

I'm copying over a worksheet from another workbook, but the formulas in B9, B10, B11, B12 and H13 reference the previous workbook, they shouldn't. I also want to mention that I'm a bit of a noob.

I've tried the following code:

    $sh2_wb2.Cells.Item(9,2).Value = "=Suivi!C6"
    $sh2_wb2.Cells.Item(9,2).Formula = '=Suivi!C6'
    $sh2_wb2.Cells.Item(9,2).Formula = "=Suivi!C6"

   $path = ($_.fullname).substring(0,($_.FullName).lastindexOf("."))
    "Adding to $path"
    $file2 = $path # destination's fullpath
    $wb1 = $excel.workbooks.open($file1, $null, $true) # open source, readonly
    $wb1.unprotect('****') # unprotect source
    $wb2 = $excel.workbooks.open($file2) # open target
    $sh2_wb2 = $wb2.sheets.item(2) # second sheet in destination workbook
    $sheetToCopy = $wb1.sheets.item(2) # source sheet to copy
    $sheetToCopy.copy($sh2_wb2) # copy source sheet to destination workbook
    $sh2_wb2.Cells.Item(9,2).Value = "=Suivi!C6"
    $sh2_wb2.Cells.Item(10,2).Value = '=1!C1'
    $sh2_wb2.Cells.Item(11,2).Value = '=1!C3'
    $sh2_wb2.Cells.Item(12,2).Value = '=1!C4'
    $sh2_wb2.Cells.Item(13,8).Value = '=1!C2'
    $wb2.protect('****')
    $wb1.close($false) # close source workbook w/o saving
    $wb2.close($true) # close destination with saving

I don't get any errors. Cell B9 from sheet 2 should show =Suivi!C6.

Upvotes: 0

Views: 75

Answers (1)

TheMadTechnician
TheMadTechnician

Reputation: 36342

When you copy a worksheet from one workbook to another the worksheet that you reference in the copy method specifies where it should place the copied sheet when it adds it to the workbook. It will insert the source sheet immediately before the specified worksheet.

So you get $sh2_wb2 = $wb2.sheets.item(2), and use it as the target. After the copy is done $sh2_wb2 is no longer Item(2), it is now Item(3). Item(2) is the new worksheet that you just copied into the workbook. Because of that, when you are changing values you are targeting the wrong worksheet. If you look at the third sheet in the destination workbook you'll notice that B9:B12 and H13 are set to what you intended to update on the sheet you just copied.

How to fix it? Re-get the second worksheet after the copy, that's the easiest way.

$path = ($_.fullname).substring(0,($_.FullName).lastindexOf("."))
"Adding to $path"
$file2 = $path # destination's fullpath
$wb1 = $excel.workbooks.open($file1, $null, $true) # open source, readonly
$wb1.unprotect('****') # unprotect source
$wb2 = $excel.workbooks.open($file2) # open target
$sh2_wb2 = $wb2.sheets.item(2) # second sheet in destination workbook
$sheetToCopy = $wb1.sheets.item(2) # source sheet to copy
$sheetToCopy.copy($sh2_wb2) # copy source sheet to destination workbook
$sh2_wb2 = $wb2.sheets.item(2) # new second sheet in destination workbook
$sh2_wb2.Cells.Item(9,2).Value = "=Suivi!C6"
$sh2_wb2.Cells.Item(10,2).Value = '=1!C1'
$sh2_wb2.Cells.Item(11,2).Value = '=1!C3'
$sh2_wb2.Cells.Item(12,2).Value = '=1!C4'
$sh2_wb2.Cells.Item(13,8).Value = '=1!C2'
$wb2.protect('****')
$wb1.close($false) # close source workbook w/o saving
$wb2.close($true) # close destination with saving

Upvotes: 0

Related Questions