Formatting all the excel comments in worksheets

[gogomarkni]

I want add one feature for formatting all the excel comments in worksheets,

after googled  , I find below vb code can set the excel comment automatically,

have someone knows how to use autoit code to do that?

(vb code look like below:)

Sub FormatAllComments()
  Dim ws As Worksheet
  Dim cmt As Comment
  For Each ws In ActiveWorkbook.Worksheets
    For Each cmt In ws.Comments
      With cmt.Shape.TextFrame.Characters.Font
        .Name = "Times New Roman"
        .Size = 12
      End With
    Next cmt
  Next ws
End Sub

#include <Excel.au3>
$oExcel = _Excel_Open()
$osBook = _Excel_BookOpen($oExcel, @ScriptDir & "\sBook.xlsx")
$otBook = _Excel_BookOpen($oExcel, @ScriptDir & "\tBook.xlsx")
For $oSheet In $osBook.WorkSheets ; process all sheets
    _ProcessSheet($oSheet)
Next

Func _ProcessSheet($oSheet)

    With $oSheet.Usedrange
        $iRows = .Rows.Count
        $iColumns = .Columns.Count
        $iSheet = $oSheet.Index
        For $i = 1 To $iRows ; Process all Rows
            For $j = 1 To $iColumns ; Process all columns
                $oComment = .Cells($i, $j).Comment
                If IsObj($oComment) Then
                    $oRange = $otBook.Worksheets($iSheet).Cells($i, $j).AddComment($oComment.Text)
                    
                     ;-- I want to insert the code to setup the type, size ..etc of comment font  
                     ; look like that, but it's wrong,
                     ;  $oComment.Shape.TextFrame.Characters.Font.Name = "Arial"
                     ;  $oCommnet.Shape.TextFrame.Characters.Font.Size = "14"
                     ;   .......add other type 


                EndIf
            Next
        Next
    EndWith
EndFunc   ;==>_ProcessSheet

Have someone knows how to setup the comment's font type by AU3?

 

[water]

Should be (untested):

If IsObj($oComment) Then
    $oNewComment = $otBook.Worksheets($iSheet).Cells($i, $j).AddComment($oComment.Text)
    $oNewComment.Shape.TextFrame.Characters.Font.Name = "Arial"
    $oNewComment.Shape.TextFrame.Characters.Font.Size = "14"    
EndIf

 

[gogomarkni]

great, it's work.

thanks, water.

seem there's nothing you can't do ~

 

 

[water]

That was easy, because the AddComment method returns a comment object, not a range.

You can do anything with COM as long as Microsoft provides the methods, properties, collections etc.