Jump to content
gogomarkni

how to read sheet comments and insert to another sheets

Recommended Posts

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 by gogomarkni

Share this post


Link to post
Share on other sites
water

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

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 by gogomarkni

Share this post


Link to post
Share on other sites
water

What's wrong with the code I posted above?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

 

 

; $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 by gogomarkni

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

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 (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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

×