Sign in
 

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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    You have to unhide the rows to copy them.

    \_(ツ)_/


    Wednesday, July 17, 2019 7:03 AM
    |
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |