gogomarkni Posted May 15, 2015 Posted May 15, 2015 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 ;==>_ProcessSheetHave someone knows how to setup the comment's font type by AU3?
water Posted May 15, 2015 Posted May 15, 2015 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
gogomarkni Posted May 15, 2015 Author Posted May 15, 2015 great, it's work.thanks, water.seem there's nothing you can't do ~
water Posted May 15, 2015 Posted May 15, 2015 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now