Cole Comfort Cole Comfort - 3 months ago 58
PowerShell Question

Copy and paste excel range between workbooks in powershell

I am trying to copy and paste an excel range between two workbooks, but the copy function is not working as I intend.
Observe the following code snippet.

$worksheetCSV.activate()
$range1=$worksheetCSV.Range("a","h")
$worksheetCSV.copy($range1)

$worksheetXLS.activate()
$lastRow = $worksheetXLS.UsedRange.rows.count + 1
$range2=$worksheetXLS.Range("A" + $lastrow)
$worksheetXLS.Paste($range2)


This code almost works but the only cell which is pasted is:

$worksheetXLS.Paste($range2)


Is there any way which I can copy the actual contents of the range to be pasted.

Answer

To just copy a range from one worksheet to another (entire columns A through H), this modification to your code would work:

$worksheetCSV.activate()
$range1 = $worksheetCSV.Range("A:H")
$range1.copy()

$worksheetXLS.activate()
$range2 = $worksheetXLS.Range("A:H")
$worksheetXLS.Paste($range2)

However, since you are adding to existing data in the other worksheet, you will have to set your target range differently so you don't overwrite what is already there. I also assumed the existing range has headers that you don't want to duplicate in the other worksheet so I started the row at 2.

Based on your current code, this should work:

$worksheetCSV.activate()
$lastRow1 = $worksheetCSV.UsedRange.rows.count
$range1 = $worksheetCSV.Range("A2:H$lastRow1")
$range1.copy()

$worksheetXLS.activate()
$lastRow2 = $worksheetXLS.UsedRange.rows.count + 1
$range2 = $worksheetXLS.Range("A$($lastRow2):H$($range1.Rows.Count)")
$worksheetXLS.Paste($range2)

To copy and paste, the size of the source range and target range should be the same. So this code sets the actual range of the data in the source worksheet (minus headers in the first row) and copies it. Then it sets the target range starting at one row higher than the used range and extending down the total number of rows in the source range (so the two range sizes should match). Finally, paste the data into the range.

Comments