Jump to content
gogomarkni

Formatting all the excel comments in worksheets

Recommended Posts

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?

 

Share this post


Link to post
Share on other sites
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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
gogomarkni

great, it's work.

thanks, water.

seem there's nothing you can't do ~

 

 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

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

×