gogomarkni Posted May 3, 2015 Share Posted May 3, 2015 (edited) Does someone know how to read the sheet comments and add to another sheets?I have searched and tried a long time but no workable method,my excel file is 2010 .xlsx , example:Read cell's value and comment in sheet 2 of sBook.xlsx, copy some of those comments to another sheet .thanks, sBook.xlsx Edited May 3, 2015 by gogomarkni Link to comment Share on other sites More sharing options...
water Posted May 3, 2015 Share Posted May 3, 2015 $oComment = $oRange.Comment If IsObj($oComment) Then $sComment = $oComment.Text 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 4, 2015 Author Share Posted May 4, 2015 water, thanks a lot.your post code is helpful for me,but i'm a newbe for coding, could you please teach me more,actually, i have two excel file, named sBook.xlsx and tBook.xlsx (source file and target file)i want to copy the cell's comment from sBook.xlsx and tBook.xlsx if the cell value in source file is same as target file.smilar to . if $oExcel.Activesheet.range(A1).value = $oExcel2.sheet (2).range(A1).value then $oExcel.sheet(2).range(A1).addComment.Text= $oExcel2.sheet (2).range(A1).comment.text loop all range A1, A2....A10....B1,B2,....H10 * the sBook.xlsx and tBook.xlsx have same cells range locationthanks, tBook.xlsx Link to comment Share on other sites More sharing options...
water Posted May 4, 2015 Share Posted May 4, 2015 I'm on a business trip right now so I will not be able to work on your issue till Friday.Depending on the size of the worksbooks it will take a long time to check each cell for comments and copy them over to the 2nd workbook.How many sheets do this workbooks have? 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 4, 2015 Author Share Posted May 4, 2015 (edited) thanks,the workbook only includes 1 -3 sheets.there are about 100 row X 300 col in one sheets, not too big.Code Performance is not first concern for me in this case , I need automation code to help me checking the cells' value and then according the result to do next process (such as copy comment..or replace value) , manually checking take a lot effort and easy wrong. Edited May 4, 2015 by gogomarkni Link to comment Share on other sites More sharing options...
water Posted May 9, 2015 Share Posted May 9, 2015 The following script works as long as the used ranges in sBook start in cell A1:#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) EndIf Next Next EndWith EndFunc ;==>_ProcessSheet 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 11, 2015 Author Share Posted May 11, 2015 hi Water,very thanks for your great help,I only do a little change and it is very useful for me,but I have one question , could you me to do some change:For $oSheet In $osBook.WorkSheets is process all sheetsif i wnat to specify certain one sheet which is selected from GUI , how to write the syntax ?e.g.I have do the front UI, user can select the index of sheet, e.g. sheet 2------------------------------------------------------------$Label1 = GUICtrlCreateLabel("Sheet Index", 60, 35, 120, 20)GUICtrlSetFont(-1, 9, 400, 0, "Arial")$input_src_sheet = GUICtrlCreateCombo("", 140, 35, 360, 20)GUICtrlSetData(-1, "Sheet1|Sheet2|Sheet3|Sheet4|Sheet5|All", "All")---------------------------------------------------------------------$oSheet = $input_src_sheet ???how can I write the syntax ? ---------modified code--------#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)NextFunc _ProcessSheet($oSheet) With $oSheet.Usedrange $iRows = .Rows.Count $iColumns = .Columns.Count $iSheet = $oSheet.Index For $j = 1 To $iColumns; Process all columns For $i = 1 To $iRows ; Process all Rows $oValue= .Cells($i, $j).Value $oComment = .Cells($i, $j).Comment $otValuse = $otBook.Worksheets($iSheet).Cells($i, $j).Value If ($otValuse = "" OR $otValuse = "-" )AND $oValue <> "" Then $otBook.Worksheets($iSheet).Cells($i, $j).Value= $oValue If IsObj($oComment) Then $oRange = $otBook.Worksheets($iSheet).Cells($i, $j).AddComment($oComment.Text) EndIf EndIf Next Next EndWithEndFunc ;==>_ProcessSheet Link to comment Share on other sites More sharing options...
water Posted May 11, 2015 Share Posted May 11, 2015 $sSelectedSheet = GUICtrlRead($input_src_sheet) If $sSelectedSheet = "All" Then For $oSheet In $osBook.WorkSheets ; process all sheets _ProcessSheet($oSheet) Next Else _ProcessSheet($osBook.WorkSheets($sSelectedSheet)) EndIfShould do the trick. I assume that the user can't input an invalid sheet name. 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 12, 2015 Author Share Posted May 12, 2015 (edited) hi water, for all index, it's ok.but for single index , I got the error in below:With $oBook.Worksheets($sSelectedSheet).UsedRangeWith ^ ERROR I divided into two functions, one is for single and another is for all index, (since I don't know how to use one function for both two )Could you help me to check where is wrong?here is the code:expandcollapse popuplocal $sSelectedSheet = GUICtrlRead($input_src_sheet) If $sSelectedSheet = "All" Then For $oSheet In $osBook.WorkSheets ; process all sheets _ProcessSheet($oSheet) Next _GUICtrlListBox_AddString($List1," Completed "&"=======> ") GUICtrlSendMsg($List1,$WM_VSCROLL,$SB_LINEDOWN,0) Else _ProcessSingleSheet($sSelectedSheet) _GUICtrlListBox_AddString($List1," Completed "&"=======> ") GUICtrlSendMsg($List1,$WM_VSCROLL,$SB_LINEDOWN,0) EndIf Func _ProcessSingleSheet($sSelectedSheet) With $oBook.Worksheets($sSelectedSheet).UsedRange $iRows = .Rows.Count $iColumns = .Columns.Count ;$iSheet = $oSheet For $j = 1 To $iColumns; Process all columns For $i = 1 To $iRows ; Process all Rows $oValue= .Cells($i, $j).Value $oComment = .Cells($i, $j).Comment $otValue = $otBook.Worksheets($sSelectedSheet).Cells($i, $j).Value $oAddress= $otBook.Worksheets($sSelectedSheet).Cells($i, $j).address If ($otValue = "" OR $otValue = "-" )AND $oValue <> "" Then $otBook.Worksheets($sSelectedSheet).Cells($i, $j).Value= $oValue If IsObj($oComment) Then $oRange = $otBook.Worksheets($sSelectedSheet).Cells($i, $j).AddComment($oComment.Text) EndIf Elseif $oValue <> "" AND $oValue <> $otValue Then $otBook.Worksheets($sSelectedSheet).Cells($i, $j).Interior.ColorIndex= 38 _GUICtrlListBox_AddString($List1," "&$oAddress&"--"& $oValue &" <> "& $otValue& " =====> Results are different!!! Please check again !!! " ) GUICtrlSendMsg($List1,$WM_VSCROLL,$SB_LINEDOWN,0) ;MsgBox (0, "Alert", $oAddress&"-----"& $oValue &" Differ To "& $otValue) EndIf Next _GUICtrlListBox_AddString($List1,"Now columns "&$j& " is processing .......... ") GUICtrlSendMsg($List1,$WM_VSCROLL,$SB_LINEDOWN,0) Next EndWith EndFunc ;==>_ProcessSheet Func _ProcessAllSheet($oSheet) With $oSheet.UsedRange $iRows = .Rows.Count $iColumns = .Columns.Count $iSheet = $oSheet.Index For $j = 1 To $iColumns; Process all columns For $i = 1 To $iRows ; Process all Rows $oValue= .Cells($i, $j).Value $oComment = .Cells($i, $j).Comment $otValue = $otBook.Worksheets($iSheet).Cells($i, $j).Value $oAddress= $otBook.Worksheets($iSheet).Cells($i, $j).address If ($otValue = "" OR $otValue = "-" )AND $oValue <> "" Then $otBook.Worksheets($iSheet).Cells($i, $j).Value= $oValue If IsObj($oComment) Then $oRange = $otBook.Worksheets($iSheet).Cells($i, $j).AddComment($oComment.Text) EndIf Elseif $oValue <> "" AND $oValue <> $otValue Then $otBook.Worksheets($iSheet).Cells($i, $j).Interior.ColorIndex= 38 _GUICtrlListBox_AddString($List1," "&$oAddress&"--"& $oValue &" <> "& $otValue& " =====> Results are different!!! Please check again !!! " ) GUICtrlSendMsg($List1,$WM_VSCROLL,$SB_LINEDOWN,0) ;MsgBox (0, "Alert", $oAddress&"-----"& $oValue &" Differ To "& $otValue) EndIf Next _GUICtrlListBox_AddString($List1,"Now columns "&$j& " is processing .......... ") GUICtrlSendMsg($List1,$WM_VSCROLL,$SB_LINEDOWN,0) Next EndWith EndFunc ;==>_ProcessSheet Edited May 13, 2015 by gogomarkni Link to comment Share on other sites More sharing options...
water Posted May 12, 2015 Share Posted May 12, 2015 What's wrong with the code I posted above? 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 12, 2015 Author Share Posted May 12, 2015 (edited) hi water ,Your original code is ok,when i tried to merge your code to mine, it's not running.-------------------error message-----==> Variable must be of type "Object".:With $oSheet.UsedRangeWith $oSheet^ ERROR------------------------------------------------------but if I change the $sSelectedSheet = 2 directly , the code is running and result is as expected.I'm finding where is incorrect in my code?I don't know why the result "2" of GUICtrlRead($input_src_sheet) is not transfer to $sSelectedSheet ? expandcollapse popup; $sSelectedSheet = 2 $sSelectedSheet = GUICtrlRead($input_src_sheet) ; If $sSelectedSheet = "All" Then For $oSheet In $osBook.WorkSheets ; process all sheets _ProcessSheet($oSheet) Next Else _ProcessSheet($osBook.WorkSheets($sSelectedSheet)) EndIf Func _ProcessSheet($oSheet) With $oSheet.UsedRange $iRows = .Rows.Count $iColumns = .Columns.Count $iSheet = $oSheet.Index For $j = 1 To $iColumns; Process all columns For $i = 1 To $iRows ; Process all Rows $oValue= .Cells($i, $j).Value $oComment = .Cells($i, $j).Comment $otValue = $otBook.Worksheets($iSheet).Cells($i, $j).Value $oAddress= $otBook.Worksheets($iSheet).Cells($i, $j).address If ($otValue = "" OR $otValue = "-" )AND $oValue <> "" Then $otBook.Worksheets($iSheet).Cells($i, $j).Value= $oValue If IsObj($oComment) Then $oRange = $otBook.Worksheets($iSheet).Cells($i, $j).AddComment($oComment.Text) EndIf Elseif $oValue <> "" AND $oValue <> $otValue Then $otBook.Worksheets($iSheet).Cells($i, $j).Interior.ColorIndex= 38 _GUICtrlListBox_AddString($List1," "&$oAddress&"--"& $oValue &" <> "& $otValue& " =====> Results are different!!! Please check again !!! " ) GUICtrlSendMsg($List1,$WM_VSCROLL,$SB_LINEDOWN,0) ;MsgBox (0, "Alert", $oAddress&"-----"& $oValue &" Differ To "& $otValue) EndIf Next _GUICtrlListBox_AddString($List1,"Now columns "&$j& " is processing .......... ") GUICtrlSendMsg($List1,$WM_VSCROLL,$SB_LINEDOWN,0) Next EndWith EndFunc ;==>_ProcessSheet Edited May 13, 2015 by gogomarkni Link to comment Share on other sites More sharing options...
water Posted May 12, 2015 Share Posted May 12, 2015 According to your script above the user would select from a list of sheet names (not sheet numbers). So as an example GUICtrlRead would return the string "Sheet1".As GUICtrlRead always returns a string Excel returns an error because it can't find a sheet named "2". You have to make sure that in this case you pass a number. 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 12, 2015 Author Share Posted May 12, 2015 (edited) sorry, I forgot told you I have modified the GUICtrlSetData to (-1, "1|2|3|4|5|All", "All") before running,it still not run, should I do another change? $Label1 = GUICtrlCreateLabel("Sheet Index", 60, 35, 120, 20) GUICtrlSetFont(-1, 9, 400, 0, "Arial") $input_src_sheet = GUICtrlCreateCombo("", 140, 35, 360, 20) GUICtrlSetData(-1, "1|2|3|4|5|All", "All") Edited May 13, 2015 by gogomarkni Link to comment Share on other sites More sharing options...
gogomarkni Posted May 12, 2015 Author Share Posted May 12, 2015 (edited) hi Water, thanks you for giving me a hint "GUICtrlRead always returns a string" ,I googling and add $iNum = Number(),now it's ok for me. If $sSelectedSheet = "All" Then For $oSheet In $osBook.WorkSheets ; process all sheets _ProcessSheet($oSheet) Next Else $iNum = Number($sSelectedSheet) _ProcessSheet($osBook.WorkSheets($iNum )) EndIf > Edited May 13, 2015 by gogomarkni Link to comment Share on other sites More sharing options...
water Posted May 12, 2015 Share Posted May 12, 2015 So your Excel workbook always has 5 sheets?If not, you need to check for the number of sheets an issue an error message when the user selects a non existing sheet. 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...
water Posted May 12, 2015 Share Posted May 12, 2015 BTW: Could you please enclose AutoIt scripts in code tags ("<>" button in the editor)? That greatly enhances readability 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