I am having below code in powershell which I am using to populate the data in excel
# Apply the formula in column O for all rows
$lastRowO = $sheet1.UsedRange.Rows.Count
for ($rowO = 2; $rowO -le $lastRowO; $rowO++) {
$cellO = $sheet1.Cells.Item($rowO, 15)
$formulaO = '=IF($N2<>"",XLOOKUP($N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
$cellO.Formula = $formulaO
#$sheet1.range("O$rowO:O$rowO").formula = '=IF($N2<>"",XLOOKUP($N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
}
when I am executing the code, it is working fine. if I go to sheet I am able to see the formula in cell but it is not populating the data.
If I manually click on the cell with the same pasted formula, it is populating the data.
Please let me know what is the issue.
Below is the entire code
"Start time is: $((Get-Date).ToString())"
$excelFilePath = "C:\VLAN.xlsx"
# Create an instance of the Excel application
$excel = New-Object -ComObject Excel.Application
try {
# Open the workbook
$workbook = $excel.Workbooks.Open($excelFilePath)
# Check if the "USER" sheet exists
$sheetExists = $false
foreach ($sheet in $workbook.Sheets) {
if ($sheet.Name -eq "USER") {
$userSheet = $sheet
$sheetExists = $true
break
}
}
if (-not $sheetExists) {
throw "Sheet 'USER' not found in the workbook."
}
# Add a new sheet named "Sheet1"
$sheet1 = $workbook.Sheets.Add()
$sheet1.Name = "Sheet1"
# Copy data from "USER" sheet to "Sheet1"
$userSheet.UsedRange.Copy()
$sheet1.Range("A1").PasteSpecial(-4163) # -4163 is the Excel constant for xlPasteAll
# Define the headers to be added starting from column N
$headers = @("BckpSG-VLanID", "Bckpnetwork-label", "BckpSubnet", "BckpNetmask", "BckpGateway", "BckpNetwork")
# Set the headers in Sheet1 starting from column N (14th column)
for ($i = 0; $i -lt $headers.Length; $i++) {
$sheet1.Cells.Item(1, 14 + $i).Value2 = $headers[$i]
}
# Apply the formula in column N for all rows
$lastRow = $sheet1.UsedRange.Rows.Count
for ($row = 2; $row -le $lastRow; $row++) {
$cellN = $sheet1.Cells.Item($row, 14)
$formulaN = '=IFERROR(IFS(MID($C' + $row + ',1,1)="2",XLOOKUP(NUMBERVALUE(MID($C' + $row + ',2,3)),BACKUP!$C:$C,BACKUP!$C:$C),MID($C' + $row + ',1,1)="3",XLOOKUP($C' + $row + '+300,BACKUP!$C:$C,BACKUP!$C:$C)),"")'
$cellN.Formula = $formulaN
}
# Apply the formula in column O for all rows
$lastRowO = $sheet1.UsedRange.Rows.Count
for ($rowO = 2; $rowO -le $lastRowO; $rowO++) {
#$cellO = $sheet1.Cells.Item($rowO, 15)
#$formulaO = '=IF($N2<>"",XLOOKUP($N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
#$cellO.Formula = $formulaO
$sheet1.range("O$rowO:O$rowO").formula = '=IF($N2<>"",XLOOKUP($N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
}
# Force Excel to calculate all formulas in the sheet
$sheet1.Calculate()
# Save and close the workbook
$workbook.Save()
$workbook.Close()
Write-Output "Sheet 'Sheet1' has been created with the 'USER' data, headers added, and formulas applied."
} catch {
Write-Error $_.Exception.Message
} finally {
# Quit the Excel application
$excel.Quit()
# Release the COM objects
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet1) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($userSheet) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
# Suppress the final output
Remove-Variable -Name sheet1, userSheet, workbook, excel
}
