Answered by:
Copy all rows grouped with powershell from an spreadsheet to another

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.
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
All replies
-
You have to unhide the rows to copy them.
\_(ツ)_/
- Edited by jrvModerator Wednesday, July 17, 2019 7:03 AM
-
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
-
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!
-
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.
\_(ツ)_/
-
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
-
-