x-spreadsheet | The project has been migrated to @ wolf-table/table https | Frontend Framework library

$Path = 'D:/ETL_Data/TwitchTVData.xlsx'
$csvPath = 'D:/ETL_Data/TwitchTVData2.csv'

# Open the Excel document and pull in the 'Sheet1' worksheet
$Excel = New-Object -Com Excel.Application
$Workbook = $Excel.Workbooks.Open($Path)
$page = 'Sheet1'
$ws = $Workbook.Worksheets | Where-Object {$_.Name -eq $page}
$Excel.Visible = $true
$Excel.DisplayAlerts = $false

# Set variables for the worksheet cells, and for navigation
$cells = $ws.Cells
$row = 1
$col = 4
$formula = @"
=NOW()
"@

# Add the formula to the worksheet
$range = $ws.UsedRange
$rows = $range.Rows.Count
for ($i=0; $i -ne $rows; $i++) {
    $cells.Item($row, $col) = $formula
    $row++
}

$ws.Columns.Item("A:D").EntireColumn.AutoFit() | Out-Null
$ws.Columns.Range("D1:D$rows").NumberFormat = "yyyy-MM-dd hh:mm"

$Excel.ActiveWorkbook.SaveAs($csvPath)
$Excel.Quit()

[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)