Microsoft Excel : Check cell's comment exist or not

[MrLee301]

Hi everyone,I'd like to check the comment on a specify cell exist or not, but it's not successful with below error, it took 2 day for searching but not found, anybody can help this problem, thanks for advance.

###########################Code##############
#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 & "\Book1.xlsx"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)

$check = $oWorkbook.Worksheets(2).Range("J10").Comment.Visible

 If not $check Then
     ConsoleWrite("Cell J10's comment not exist")
Else
ConsoleWrite("Cell J10's comment not exist")
 EndIf
 
 ConsoleWrite($check)

########### OUTPUT #########
: ==> The requested action with this object has failed.:
$check = $oWorkbook.Worksheets(2).Range("J10").Comment.Visible
$check = $oWorkbook.Worksheets(2).Range("J10").Comment^ ERROR

 

Edited January 23, 2024 by Jos
added codebox

[ioa747]

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

Local $oExcel, $oWorkbook

$oExcel = _Excel_Open()
;~ $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test1.xlsx")
$oWorkbook = _Excel_BookNew($oExcel)

Local $iStep = 1, $sTip[6][2]
$sTip[0][0] = 5

For $i = 1 To $sTip[0][0]
    $sTip[$i][0] = "A" & $iStep
    $sTip[$i][1] = "Tip no (" & $i & ")"
    $oWorkbook.Worksheets(1).Range($sTip[$i][0]).AddComment.Text($sTip[$i][1])
    $oWorkbook.Worksheets(1).Comments($i).Visible = True
    $iStep += 3
    Sleep(500)
Next

Sleep(2000)

ConsoleWrite("Comments count = " & $oWorkbook.Worksheets(1).Comments.count & @CRLF)

For $i = 1 To $sTip[0][0]
    $oWorkbook.Worksheets(1).Comments($i).Visible = False
    Sleep(500)
Next

Sleep(2000)

For $i = 1 To $sTip[0][0]
    $oWorkbook.Worksheets(1).Range($sTip[$i][0]).ClearComments
    Sleep(500)
Next

 

[water]

$oWorkbook.Worksheets(1).Range("B2").Specialcells($xlCellTypeComments) ; -4144 Cells containing notes.

Returns a Range holding all cells with comments. This way you can check for a comment of a single cell as well.

[MrLee301]

Test1.xlsx

Sorry, i need to insert my excel file to make it clear, the user create that file and add comment manually as below picture, i may check comment exist on cells: A1,A2,A3,A4... -> get content and assign to an array like this: "content | comment"
###########

Local Array aArray[10]

aArray[0]="|"; read from (A1)
aArray[1]="abc1|"; read from (A2)
aArray[2]="abc2|"; read from (A3)
aArray[3]="abc3|comment1"; read from (A4)
..........

Thank you @ioa747 and @water but it's not solved this matter yet.

 

[Nine]

Try this :

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkBook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test1.xlsx")
Local $oRange = $oWorkBook.Sheets(1).Range("A1:A8")
Local $aList[$oRange.count], $i = 0, $oComment
For $oCell in $oRange
  $aList[$i] = $oCell.value
  $oComment = $oCell.comment
  $aList[$i] &= "|" & (IsObj($oComment) ? $oComment.text : "")
  $i += 1
Next
_ArrayDisplay($aList)

 

Edited January 23, 2024 by Nine

[MrLee301]

IsObj($oComment) -> that's the point, you save my day, thank you. @Nine