how to read sheet comments and insert to another sheets

[gogomarkni]

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

[water]

$oComment = $oRange.Comment
If IsObj($oComment) Then $sComment = $oComment.Text

 

[gogomarkni]

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 location

thanks,

 

 

 

 

 

 

 

tBook.xlsx

[water]

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?

[gogomarkni]

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

[water]

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

 

[gogomarkni]

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 sheets
if 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)
Next

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
                $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
    EndWith
EndFunc   ;==>_ProcessSheet

[water]

$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

Should do the trick. I assume that the user can't input an invalid sheet name.

[gogomarkni]

hi water,

 

for all index, it's ok.

but for single index , I got the error in below:

With $oBook.Worksheets($sSelectedSheet).UsedRange
With ^ 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 popup

local $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

[water]

What's wrong with the code I posted above?

[gogomarkni]

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.UsedRange
With $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

[water]

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.
 

[gogomarkni]

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

[gogomarkni]

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

[water]

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.

[water]

BTW: Could you please enclose AutoIt scripts in code tags ("<>" button in the editor)? That greatly enhances readability