Powershell - Copy and pasting last lines of TSV to specific Cells in Excel

Ok, so 3rd time is hopefully going to get me there. I have been working on this script for some time and just need a little help to get me over the finish line. I am trying to write a script for powershell that takes the last X lines (in this case 8 lines) of a TAB-separated file and pastes it into a specific cell in an Excel template I have created. I have tried this in batch and VBS to no avail.

So far this is the code I've come up with:

 $pathtsv = “C:\test.txt” | Select -Last 8
 $pathxlsx = “C:\NBP ESP-152 REV F TEMPLATE.xlsx”
 $Excel = New-Object -ComObject excel.application
 $Excel.visible = $true
 $Workbook = $excel.Workbooks.open($pathtsv)
 $Workbook2 = $excel.Workbooks.open($pathxlsx)
 $Worksheet = $Workbook.WorkSheets.item(“Overview”)
 $Worksheet.activate()
 $range = $WorkSheet.Range(“A3:J10”).CurrentRegion
 $range.Copy() | out-null
 $Worksheet2 = $Workbook2.Worksheets.item(“RAW DATA”)
 $worksheet2.activate()
 $range2 = $Worksheet2.Range(“A3:A3”)
 $Worksheet2.Paste($range2)
 $Excel.Quit()
 [gc]::collect()
 [gc]::WaitForPendingFinalizers()

Here is an example of the TSV file from which I am trying to copy:

09/29/17    12:49:31    NBP 00022   10.013  5955.000    7.198   0.309   24.017  60.658  CW          
            20.057  0.000   0.091   0.000   0.000   0.000   CW
            31.050  5954.000    7.094   0.302   24.016  61.432  CCW
            41.083  0.000   0.547   0.000   0.000   0.000   CCW
            47.081  0.000   78.460  2.104   4.515   0.000   CW
            52.099  0.000   82.710  2.156   4.516   0.000   CCW
            57.234  0.000   103.000 2.858   6.217   0.000   CW
            62.247  0.000   111.000 2.887   6.216   0.000   CCW

Here is what the template looks like I am trying to paste into (@ A, 3).

highlight shows where it is to be pasted

Solution

This worked for me... you may have to alter it a bit depending on what you named your sheets:

$pathtsv = "\test.txt"
$pathxlsx = "\NBP ESP-152 REV F TEMPLATE.xlsx"

$Excel = New-Object -ComObject "Excel.Application"
$Excel.Visible=$true 

$Workbook = $Excel.Workbooks.Open($pathxlsx) # Open Template
$TempWorkbook = $Excel.Workbooks.Opentext($pathtsv) # Open text file in excel

$temp = $excel.Workbooks.Item(2)  #select workbook with text
$temp = $temp.Worksheets.Item(1) #select text worksheet
$CopyRange = $temp.Range("A1:G8") #set range
$CopyRange.Copy()  #copy data

$workbooksheet = $Workbook.Worksheets.Item(1)#sets doc to copy to
$Workbooksheet.activate()
$PasteRange = $workbooksheet.Range("A3:J10") #sets range
$workbooksheet.Paste($PasteRange)#paste data

#save and close the workbook
$Workbook.Close($true)
$Excel.Quit()
while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)){}
[gc]::collect()
[gc]::WaitForPendingFinalizers()

Note that $Excel.Workbooks.Opentext does not return anything it only opens it in excel.