$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)
$Path = 'D:/ETL_Data/TwitchTVData.xlsx'
$csvPat