Working with Excel COM in PowerShell

# Path to CSV
$CSV = "C:\some folder\CSV.csv"
# Path to Report blank file
$Report = "C:\some folder\Report.xls" # May work with xlsx but havent tried
# Add Items to CSV
Add-Content $CSV $VariableList
Add-Content $CSV $VariableList1
Add-Content $CSV $VariableList2
Add-Content $CSV $VariableList3
Add-Content $CSV $VariableList4
Add-Content $CSV $VariableList5

## Work with Excell
# Copy Data from CSV
## Open CSV and copy to clipboard
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Workbook = $excel.Workbooks.open($CSV)
$Worksheet = $Workbook.WorkSheets.item(“Sheet1”)
$worksheet.activate()
$range = $WorkSheet.Range(“A2:C32”)
$range.Copy() | out-null
$Excel.Quit()
Remove-Variable -Name excel
[gc]::collect()
[gc]::WaitForPendingFinalizers()

## Open tonight's report and past clipboard data
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Workbook = $excel.Workbooks.open($Report)
$Worksheet = $Workbook.WorkSheets.item(“Night Report”)
$worksheet.activate()
$Range = $Worksheet.Range(“A1”)
$Worksheet.Paste($Range)
$workbook.Save()
$Excel.Quit()
Remove-Variable -Name excel
[gc]::collect()
[gc]::WaitForPendingFinalizers()

# Some notes: Add-Content if i remeber correctly just adds to the previous underneath, once you are done concatting variables you need to know the range for pasting ie 6 add-contents might equal 45 rows of data in the variable, when pasting into the xls you would specify the range of A1:A45. if you dont or wont know this you can do a $CSV.count and for range specify A1:$($CSV.count, OR do a longer than needed range ie A1LA1200 but I dont remember if that gives error or not). Also note that above I have ommitted other code blocks of the same info, youll notice my copy does A2:C42, my CSV was exported from NetApp with other Data, so you can copy more than one single row but in the example above im doing 1 row, I left my range to clue you in that more can be done, change as necessary