Welcome › Forums › General PowerShell Q&A › How to add comments/notes to excel file cell
- This topic has 4 replies, 2 voices, and was last updated 10 months ago by
Participant.
-
AuthorPosts
-
-
March 27, 2020 at 12:43 pm #212883
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 -
March 28, 2020 at 1:11 pm #213012
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
PowerShell12345678910111213141516$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() -
March 28, 2020 at 1:15 pm #213015
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:
PowerShell12345678910111213141516$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() -
March 28, 2020 at 7:29 pm #213060
Hey, yeah it looks like importexcel module does not have capability to do it. Thanks for the solution 🙂
-
-
AuthorPosts
- The topic ‘How to add comments/notes to excel file cell’ is closed to new replies.