How to add comments/notes to excel file cell

Welcome Forums General PowerShell Q&A How to add comments/notes to excel file cell

Viewing 3 reply threads
  • Author
    Posts
    • #212883
      Participant
      Topics: 1
      Replies: 1
      Points: 0
      Rank: 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 Image

       

      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

    • #213012
      Participant
      Topics: 48
      Replies: 194
      Points: 767
      Helping Hand
      Rank: 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()
      
      

       

    • #213015
      Participant
      Topics: 48
      Replies: 194
      Points: 767
      Helping Hand
      Rank: 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()
      
      
    • #213060
      Participant
      Topics: 1
      Replies: 1
      Points: 0
      Rank: Member

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

Viewing 3 reply threads
  • You must be logged in to reply to this topic.