BBs19

Reading Excel comments from a range all at once

25 posts in this topic

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?

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

#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 by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

@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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

 @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 by BBs19

Share this post


Link to post
Share on other sites

@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?


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by water
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

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 :thumbsup:

Edited by Jfish
1 person likes this

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 :thumbsup:

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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now