gogomarkni Posted May 15, 2015 Share 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? Link to comment Share on other sites More sharing options...
water Posted May 15, 2015 Share 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gogomarkni Posted May 15, 2015 Author Share Posted May 15, 2015 great, it's work.thanks, water.seem there's nothing you can't do ~ Link to comment Share on other sites More sharing options...
water Posted May 15, 2015 Share 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
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