none
Copy all rows grouped with powershell from an spreadsheet to another RRS feed

  • Question

  • Hi folks

    I am trying to copy data from one sheet to another. In the first sheet i have some rows. Every visible row has grouped  folded rows.

    I try to copy all rows using this code

    $path = "C:\Users\john\Desktop\wb.xlsx" $Excel = New-Object -ComObject excel.application $Excel.visible = $false $Workbook = $excel.Workbooks.open($path) $Worksheet = $Workbook.WorkSheets.item(“Sheet1”) $worksheet.activate() $range = $WorkSheet.Range(“A:G”).EntireColumn $range.Copy() | out-null $Worksheet2 = $Workbook.Worksheets.item("Sheet2") $Range = $Worksheet2.Range(“A:G”) $Worksheet2.Paste($range) $workbook.Save() $Excel.Quit() Remove-Variable -Name excel [gc]::collect() [gc]::WaitForPendingFinalizers()

    but executing it, only are copied the visible rows, losing the hidden folded rows, belonging to each visible row

    So , please anybody suggest anyway to copy the cells including the gropped folded rows asociated to the visible cells?.

    your help is much appreciated, BR.

    Wednesday, July 17, 2019 6:42 AM

Answers

  • This is the correct way to copy and paste:

    $filepath = 'C:\Users\john\Desktop\wb.xlsx'
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wb = $xl.Workbooks.open($filepath)
    if($wb.WorkSheets['Sheet1'].UsedRange.Copy()){
        $wb.Worksheets['Sheet2'].Paste()
        $wb.Save()
    }else{
        Write-Host 'Object not found'
    }
    $xl.Quit()
    


    \_(ツ)_/

    • Marked as answer by powerswitch Tuesday, July 30, 2019 12:59 PM
    Wednesday, July 17, 2019 7:18 AM
    Moderator

All replies

  • You have to unhide the rows to copy them.


    \_(ツ)_/


    Wednesday, July 17, 2019 7:03 AM
    Moderator
  • This is the correct way to copy and paste:

    $filepath = 'C:\Users\john\Desktop\wb.xlsx'
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wb = $xl.Workbooks.open($filepath)
    if($wb.WorkSheets['Sheet1'].UsedRange.Copy()){
        $wb.Worksheets['Sheet2'].Paste()
        $wb.Save()
    }else{
        Write-Host 'Object not found'
    }
    $xl.Quit()
    


    \_(ツ)_/

    • Marked as answer by powerswitch Tuesday, July 30, 2019 12:59 PM
    Wednesday, July 17, 2019 7:18 AM
    Moderator
  • Hi jrv,

    yeah the code works fine as i provided, unfortunately the grouped rows asociated to visible rows arent't copied, only visible cells :(

    source sheet

    https://imgur.com/C4EAGKT

    destiny sheet

    https://imgur.com/xt6wRWo

    could you help me please?

    TVM , BR!

    Wednesday, July 17, 2019 10:23 AM
  • Without your spreadsheet the only thig I can say is to unhide the rows.

    Did you even try my code?  You code has some issues with the way it is copying and what it is copying. Your code may do something but it does nlot do what you want because it is not how Excel works.


    \_(ツ)_/

    Wednesday, July 17, 2019 11:05 AM
    Moderator
  • Hi jrv,

    i used your code and i take as best method, but as you said, i have to unhide the rows ( like Alt -> A -> J in excel window mode ) but doing with powershell.

    This is the target that i am trying to achieve, but if is not possible, is nothing to do.

    do you think this is possible in powershell ?

    thanks, BR

    Wednesday, July 17, 2019 2:36 PM
  • I can copy hidden rows with my code with no issue. The rows do not remain hidden after the copy.


    \_(ツ)_/

    Wednesday, July 17, 2019 8:07 PM
    Moderator
  • Hi friend,

    sorry, i was doing bad , your code is working perfect!!.

    i am really grateful to you.

    thanks, BR.

    Thursday, July 18, 2019 8:23 AM