Reading Excel comments from a range all at once

[BBs19]

Hi guys,

is there any way to read comments from a defined range all at once like with the _Excel_RangeRead function?

I have tried editing the _Excel_RangeRead function to also read comments from a range, but it won't work. I guess reading comments for ranges is just not supported.

It really sucks reading comments one by one on a huge excel file.

Is there any other way you guys know of?

[Jfish]

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $sWorkbook = @ScriptDir & "\comments.xlsx"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

;$cmt = $oWorkbook.Worksheets(1).Comments
$cmt = $oWorkbook.ActiveSheet.Comments
For $comments In $cmt
 ConsoleWrite(@crlf&"This is a comment: " &$comments.text&@crlf)
Next

 

Edited August 14, 2015 by Jfish

[water]

For a range you can only access the "comment associated with the cell in the upper-left corner of the range." (According to MS).
You can only process all comments of a worksheet and check the address being inside your range.

[Jfish]

I think @water is right about getting all the comments for a given range as opposed to the whole sheet (my miss on the OP).  However, this works for a cell:

$cmt = $oWorkbook.Worksheets(1).Range("A2:A2").Comment.text

So if you could loop through the range you could possibly build it that way.  It would be slower - but it should be able to get them all for you - for your desired range.

Edited August 14, 2015 by Jfish

[water]

@All

How often do you need to extract comments from a given Range? Grabbing all comments from a worksheet is easy, but more complex for a Range.
Should this be added to the UDF?

[Jfish]

I have never needed to do it ... but it is a cool idea.  Could you do this with _Excel_RangeRead?

    1 - Value (default)

    2 - Formula

        3 - The displayed text

    4 - Comment

Edited August 14, 2015 by Jfish

[water]

That would be the plan - but it depends on how complex it gets.

[BBs19]

 @Jfish

That is helpful, but i need to know to which cell it belongs for my program. Looping through single cells is something that i am trying to avoid. I have been doing it like that before, but it takes too much time if you need to read a big Excel file.

You can only process all comments of a worksheet and check the address being inside your range.

What do you mean by check the address being inside your range? Does this mean you can tell to which cell the comment belongs when reading all comments of a worksheet? If so, that would allready have helped me.

Edited August 14, 2015 by BBs19

[Jfish]

@BBs19

but i need to know to which cell it belongs for my program

You would.  You need to loop the range with cells - using variables.  If the range is A1:C10 then you would have to walk each cell in the loop.  However, by doing that you already know the cells you are referencing and could push them to an array with the comments.  I agree it could take longer to run that loop but I don't think the COM supports the return of a collection for the range - only for the whole sheet.  Therefore, I am pretty sure @water would be implementing something similar if he were to modify the UDF (he can confirm).  Does that make sense?

[water]

Untested:

$oRange = Your range
$oRangeWithComments = $oWorksheet.Cells.SpecialCells($xlCellTypeComments)
For $oCell in $oRangeWithComments
    If $oExcel.Intersect($oCell, $oRange) Then 
        ConsoleWrite("Address: " & $oCell.Address & ", Comment: " & $oCell.Comment.Text & @CRLF)
    EndIf
Next

 

Edited August 14, 2015 by water

[Jfish]

Is that Intersect method supposed to be called from an Excel object?

[water]

Correct. It's a method of the application object. I fixed the above code.

[Jfish]

Is there any chance you added a reference to

$xlCellTypeComments

To your UDF - or maybe I don't have a current one ...

Found it - my bad.

Edited August 14, 2015 by Jfish

[BBs19]

Am i doing something wrong?

Local $oExcel = _Excel_Open()
Local $sWorkbook = "path to my .xls file"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True)

$oWorksheet = $oWorkbook.WorkSheets.Item(1)

$oRange = "E17:GB40"
$oRangeWithComments = $oWorksheet.Cells.SpecialCells($xlCellTypeComments)
For $oCell in $oRangeWithComments
    If  $oExcel.Intersect($oCell, $oRange) Then 
        ConsoleWrite("Address: " & $oCell.Address & ", Comment: " & $oCell.Comment.Text & @CRLF)
    EndIf
Next

 

 ==> The requested action with this object has failed.:
If  $oExcel.Intersect($oCell, $oRange) Then
If  $oExcel^ ERROR

 

[water]

You need a range object. So I think it should be:

$oRange = $oWorksheet.Range("E17:GB40")

 

[Jfish]

I could not get the test to recognize the intersection as written it won't evaluate to true (I think because it returns a range object not true).  I also noticed it may be missing "Application" after the application object and before the intersect method.  @water please feel free to correct me if I did this wrong - or could have done it better - but this works for me.

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\comments.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True)

$oWorksheet = $oWorkbook.WorkSheets.Item(1)
$oRange = $oWorksheet.Range("A1:A3")
$oRangeWithComments = $oWorksheet.Cells.SpecialCells($xlCellTypeComments) ; returns a range

For $oCell in $oRangeWithComments
    $oIntersect = $oExcel.Application.intersect($oRange, $oRangeWithComments)
    If Not IsObj($oIntersect) Then
    ConsoleWrite("no intersection"); used for negative testing when I played with the ranges
    Else
    ConsoleWrite("Address: " & $oCell.Address & ", Comment: " & $oCell.Comment.Text & @CRLF)
    EndIf
Next

P.S. That intersect method is awesome.  I learn a ton from all your posts

Edited August 14, 2015 by Jfish

[water]

Is there any chance you added a reference to

$xlCellTypeComments

To your UDF - or maybe I don't have a current one ...

Found it - my bad.

The UDF only contains those constants which are used in the UDF. Excel knows a lot more.

[water]

I could not get the test to recognize the intersection as written it won't evaluate to true (I think because it returns a range object not true).  I also noticed it may be missing "Application" after the application object and before the intersect method.  @water please feel free to correct me if I did this wrong - or could have done it better - but this works for me.

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\comments.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True)

$oWorksheet = $oWorkbook.WorkSheets.Item(1)
$oRange = $oWorksheet.Range("A1:A3")
$oRangeWithComments = $oWorksheet.Cells.SpecialCells($xlCellTypeComments) ; returns a range

For $oCell in $oRangeWithComments
    $oIntersect = $oExcel.Application.intersect($oRange, $oRangeWithComments)
    If Not IsObj($oIntersect) Then
    ConsoleWrite("no intersection"); used for negative testing when I played with the ranges
    Else
    ConsoleWrite("Address: " & $oCell.Address & ", Comment: " & $oCell.Comment.Text & @CRLF)
    EndIf
Next

P.S. That intersect method is awesome.  I learn a ton from all your posts

I couldn't test my code before posting - Ubuntu doesn't run Excel
If it works then it is perfect  

[water]

Excel is so powerful - I only know a little bit of it.
If I have a problem I ask Google and add "visual basic". The result can then be easily be translated to AutoIt.

[BBs19]

Thanks for the effort guys. It works like this, but the performance is not what i was really hoping for.

With the same document, reading the cells of the same range with the rangeread function takes about 75ms.

Reading the comments on the other hand takes over 4000ms which won't work for my case.

I am actually using it to read a shift schedule for the whole year from an Excel file. This way i made a month-calendar like program to show the shift for each day including the comments for each day. But the startup of the program would just take too much time if i wanted to read all of the comments into an array using the script above. 

Thanks anyways