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: 52 Replies: 211 Points: 890Rank: 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

PowerShell

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

$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: 52 Replies: 211 Points: 890Rank: 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:

PowerShell

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

$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