Reading comments from Excel file with PowerShell

Question

My searches only led to something similar in VBA, but I have a different approach and in PowerShell for this problem.

I am new to PowerShell and I want to read all comments in Excel files and save them to a CSV file. Everything works except for reading the comment. The comment is a com object. How do I get the actual comment from the com object? Here is a snippet of the relevant code. I'm not able to do it with invokemember but perhaps I am doing it wrong. Thanks in advance.

ForEach ($File in (Get-ChildItem $folder -Include *.xls, *.xlsx, *.xlsm -Recurse))
{
   $sh=$document.Sheets.Item($i)
   $comments = $sh.comments
   foreach ($comment in $comments)
   {
       #[System.__ComObject] <--- Must get this value | Add-Content -file.csv
   }  
}

I am also open to other methods to accomplish this.

score 1 · Answer 1 · answered Jun 28 '17 at 21:39

1

Each Comment Object has a Text method which returns or updates the information.

I took that a step further and split on the newline so you can separate out the comment from the author:

foreach ($comment in $comments) {
    $CommentText = $comment.text().split([environment]::newline)
    [pscustomobject]@{
        'Author' = $CommentText[0]
        'Text' = $CommentText[1]
    }
}

answered Jun 28 '17 at 21:39

BenH

9,766
1
22
35

Thanks, but $comment.text() causes this error: Method invocation failed because [System.__ComObject] does not contain a method named 'text'. – Rhamnetin Jun 30 '17 at 17:59

score 0 · Answer 2 · answered Jun 28 '17 at 21:56

I'm sure there are a boatload of ways to get this done...

import-module psexcel
$fred = new-object OfficeOpenXml.ExcelPackage -ArgumentList "C:\Users\mccarthyd\Documents\Book1.xlsx"

foreach ($worksheet in $fred.Workbook.Worksheets) {
    $worksheet.comments
}

see this post: powershell excel access without installing Excel

Reading comments from Excel file with PowerShell

2 Answers2