Scheduled script adding to new cells in xlsx

Question

I am trying replicate an end users experience by monitoring the time it takes to copy a large file off of a network directory.

I'm using Measure-Command to find the total time it takes to copy an item from a network directory onto the computer that this script is scheduled on (using Windows Scheduler) and output the time in an xlsx.

The issue I'm running into is that every time this script runs off of the scheduler (daily), it overwrites the previous day's data instead of posting the result in the next cell. When I run it manually multiple times, it works just fine and posts separate results under each other. I think the issue is that it's going by the instance (so running the code a handful of times in the same PowerShell instance sees that the $previousRow works, but in the daily schedule it opens a new instance every time and writes over the old data in cell (1,1) and (1,2).

Any suggestions on how to keep historical data?

$seconds = Measure-Command {
    Copy-Item -Path X:\shareddrive\test.pdf -Destination C:\Users\Me\Desktop
} | select TotalSeconds

$erroractionpreference = "SilentlyContinue"
$a = New-Object -ComObject Excel.Application
$dt = Get-Date -Format "MM/dd/yyyy hh:mm:ss"
$a.Workbooks.Open("X:\shareddrive\output\timesample.xlsx")
$a.Visible = $true
$a.Worksheets.Item(1)

$previousRow += 1
$a.Cells.Item($previousRow,1) = "OfficeLocation - " + $dt
$a.Cells.Item($previousRow,2) =  $seconds.TotalSeconds

$a.ActiveWorkbook.Save()
$a.Workbooks.Close()
$a.Quit()
Remove-Item -Path C:\Users\Me\Desktop\test.pdf

Answer 1

Not sure if you haven't copied all your code in? But it looks like you aren't actually defining $previousRow anywhere? So your existing code runs $previousRow += 1 and that sets $previousRow to 1... which means each time the code runs it will hit row 1 first

To be able to find the row that has the last information in it (ie the value to set $previousValue to + 1) you can use this code:

$filepath = "C:\Folder\ExcelFile.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $False
$WorkBook = $objExcel.Workbooks.Open($filepath)
$WorkSheet = $objExcel.WorkSheets.item(1)
$WorkSheet.activate()

[int]$lastRowvalue = ($WorkSheet.UsedRange.rows.count + 1) - 1
$lastrow = $WorkSheet.Cells.Item($lastRowvalue, 1).Value2
write-host $previousValue 
write-host $lastRowvalue

Copy/paste from here (with a few slight modifications): To get the value of last cell used in Excel

That tells you the last row with data... so you would need to add 1 to that first before you set it as the value for $previousRow:

$previousRow = $previousRow + 1