Search code examples
excelpowershellcopy-paste

Last 8 used rows in excel. Powershell

I've looked all over and I can't seem to find an answer for this. Instead of selecting from A1 to G8, I just want it to select the last 8 USED rows in the import it creates. Not sure what to use here. 

$pathtsv = "C:\xxxxxx.mdf"
$pathxlsx = "C:\xxxxxxxxxxxxxx.xlsx"

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

$Workbook = $Excel.Workbooks.Open($pathxlsx) 
$TempWorkbook = $Excel.Workbooks.Opentext($pathtsv) 

$temp = $excel.Workbooks.Item(2) 
$temp = $temp.Worksheets.Item(1) 
$CopyRange = $temp.Range("A1:G8") 
$CopyRange.Copy()

Thanks in advance.

Solution

  • I'd recommend using the ImportExcel module by Doug Finke rather than using Excel as a COM Object. Then the Excel document can be easily imported as a PowerShell object which can then be filtered by Select-Object -Last 8 or $ImportedExcelObject[-8..-1] then reexported in what I assume a Tab Delimited format.

    Install-Module ImportExcel

$pathtsv = "C:\xxxxxx.mdf"
$pathxlsx = "C:\xxxxxxxxxxxxxx.xlsx"

Import-Excel $pathxlsx | Select-Object -Last 8 | Export-CSV $pathtsv -Delimiter "`t"

    Note: The Install-Module command is included by default in PowerShell 5+.

    Here is the updated code for going the opposite way from a tsv to a a specific row and column in an Excel document using the Export-Excel cmdlet from the ImportExcel Module.

    Install-Module ImportExcel

$pathtsv = 'C:\xxxxxx.mdf'
$templatexls = 'C:\yyyyyyyyyy.xlsx'
$pathxlsx = 'C:\xxxxxxxxxxxxxx.xlsx'

Copy-Item $templatexls $pathxlsx
Import-CSV $pathtsv -Delimiter "`t" | Select-Object -Last 8 | Export-Excel -Path $pathxlsx -WorksheetName 'Sheet1' -Show -StartRow 3 -StartColumn 1 -NoHeader