Hello everyone,
I am trying to add comments/notes to excel file cell, right now i am using ImportExcel module. I am encountered with these problems:
- I am using AddComments method which should add note with text and author, but it only adds the note text.
- When i try to overwrite comment I can't, since I get excel file error
Script that I am using:
Install-Module Install-Module -Name ImportExcel -RequiredVersion 7.0.1
$FileName = “FileName.xlsx”
$ExcelFile = Open-ExcelPackage -Path PathToTheFile$FileName
$WorkSheet = $ExcelFile.Workbook.Worksheets[“SheetName”]
$WorkSheet.Cells[“A4”].AddComment(“Hello world”, “Jonas”)
Close-ExcelPackage $ExcelFile -Show
I wasn’t able to figure out how to do this using the importexcel module. But using the com object Excel.Application, i was able to do it like this
$file = "C:\temp\ssis.xlsx"
$Sheet = 'sheet4'
$Range = 'A11'
$Item = '1,11'
$xl = New-Object -com Excel.Application
$xl.visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open($file )
$ws = $wb.worksheets | ?{$_.name -eq $Sheet}
$ws.Cells.Item($Item) = 'A value in cell $Range.'
[void]$ws.Range($Range).AddComment()
$ws.Range($Range).comment.Visible = $False
[void]$ws.Range($Range).Comment.text("IB: `r this is a comment")
$Ws.saveAs($file)
$ws.close
$xl.Quit()
I couldn’t find how to do this using the importexcel module but with the excel com object, I was able to do it like this:
$file = "C:\temp\ssis.xlsx"
$Sheet = 'sheet4'
$Range = 'A11'
$Item = '1,11'
$xl = New-Object -com Excel.Application
$xl.visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open($file )
$ws = $wb.worksheets | ?{$_.name -eq $Sheet}
$ws.Cells.Item($Item) = 'A value in cell $Range.'
[void]$ws.Range($Range).AddComment()
$ws.Range($Range).comment.Visible = $False
[void]$ws.Range($Range).Comment.text("IB: `r this is a comment")
$Ws.saveAs($file)
$ws.close
$xl.Quit()
Hey, yeah it looks like importexcel module does not have capability to do it. Thanks for the solution