Reading comments from an Excel file using PowerShell

Question

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

I am new to PowerShell and I want to read all comments in Excel files and save them in a CSV file. Everything works except reading the comment. Comment is a com object. How to get the actual comment from a com object? Here is a snippet of the corresponding code. I can't do this with invokemember, but maybe I'm 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 for this.

Answer 1

.

, :

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

Answer 2

, ...

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

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

. : powershell Excel