How to add comments/notes to excel file cell

[Jonauskas96 Participant Topics: 1 Replies: 1 Points: 0Rank: Member]

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:

1. I am using AddComments method which should add note with text and author, but it only adds the note text.
2. 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

[Iain Participant Topics: 48 Replies: 194 Points: 767Rank: Major Contributor]

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()

 

[Iain Participant Topics: 48 Replies: 194 Points: 767Rank: Major Contributor]

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()

[Jonauskas96 Participant Topics: 1 Replies: 1 Points: 0Rank: Member]

Hey, yeah it looks like importexcel module does not have capability to do it. Thanks for the solution 🙂

[Author]

Posts