Getting values from excel column's Comment.

[Ram]

Hi,

I am looking for an example where I need to read the comments on a column in an excel file. Is this possible? if so how do I go about doing it??

Awaiting your response!

Thanks!

[GEOSoft]

Hi,

I am looking for an example where I need to read the comments on a column in an excel file. Is this possible? if so how do I go about doing it??

Awaiting your response!

Thanks!

Start by doing a search in the example scripts forum for Excel

There are at least two UDF's but I'm not sure if they have anything for comments or not. I do know that they can be read by creating a COM object.

[Ram]

Start by doing a search in the example scripts forum for Excel

There are at least two UDF's but I'm not sure if they have anything for comments or not. I do know that they can be read by creating a COM object.

Yep did search through all the UDF's example but didn't get hold of any example for my issue hence posting this issue..!!

[DaleHohm]

The UDF's will help you get a cell reference. Once you have that, it will have a .Comment property, which in turn has a .text property

A full reference in VBScript (taken from Excel Help) can look like:

Worksheets(1).Range("E5").Comment.Text

This may still not be clear, so I suggest you take a shot at it and post some code and you'll get the rest of the help you need.

Dale

[Locodarwin]

Using my collection of functions, found at the link in my signature:

; Load the comments of the first 20 lines of column 1 into an array

#include <ExcelCOM_UDF.au3>
#include <Array.au3>

Global $aComments[21]

$oExcel = _ExcelBookOpen("C:\Path\YourExcelFile.xls")

For $xx = 1 To 20
    $aComments[$xx] = _ExcelCommentRead($oExcel, $xx, 1)
Next

_ArrayDisplay($aComments, "Here Are The Comments")

Good luck in your Excel endeavors!

-S

[Keltset]

Hello,

If anyone has a spare moment a bit of guidance would be greatly appreciated.

I have been looking for this solution when I came across this thread. I have been able to successfully use Locodarwin's UDF when I know that there is a comment in the cell, however when I try to use it on a cell without a comment it breaks my script. I was hoping someone may be able to assist me in solving this problem:

#include <Include\ExcelCOM_UDF.au3>
#include <Include\Array.au3>
$oExcel = _ExcelBookOpen("\\192.168.1.10\path\to\excel.xlsm")
$aComments = _ExcelCommentRead($oExcel, "AV144")

ConsoleWrite($aComments & " <<Here Are The Comments" & @LF)
$aComments = _ExcelCommentRead($oExcel, "AV143")
ConsoleWrite($aComments & " <<Here Are The Comments" & @LF)

The output I get is:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "\\192.168.1.10\f\path\to\au3testfiles\test.au3"

me:

3827 This is the comment I put in this cell <<Here Are The Comments

\\192.168.1.10\f\path\to\au3testfiles\Include\ExcelCOM_UDF.au3 (1525) : ==> Variable must be of type "Object".:

Return $oExcel.Range($sRangeOrRow).Comment.Text

Return $oExcel.Range($sRangeOrRow).Comment^ ERROR

>Exit code: 1 Time: 13.862

I have tried a various number of different things such as wrapping an if else statement around IsObj to determine if it was an object or not as well as if/then's around @error checking but I can't seem to get it to not break if there is no cell comment present, and I am uncertain how else to look for a comment.

[water]

Keltset,

this thread is 5 1/2 years old. It would be best to open a new one.

Your script needs some error checking. Was _Excelbookopen successfull and did it return an object?