How to read left side cell value to the comment cell

gsr
not to sure what you need but this will show the comments

For Each mySht In Worksheets
    For Each mycomment In Worksheets(mySht.Name).Comments
     MsgBox mycomment.Text

    Next mycomment
Next mySht

Do you know where all the comments are?

There are lot of sheets in workbook and most of the sheets has bunch of comments. I used below code to read all the comments in workbook.

Open outFilePath For Output As #1
    For Each mySht In wkb.Worksheets
      Print #1, vbCrLf & mySht.Name & vbCrLf
      For Each mycomment In Worksheets(mySht.Name).Comments
        Print #1, "From " & mycomment.Parent.Parent.Name _
            & mycomment.Parent.Address _
            & " Comes the comment: " _
            & mycomment.Text
                                
    Next mycomment
Next mySht

However, I need to read cell value along with comments.
ex:
column next to the comment cell
2 columns over from the comment cell

Range("E9:G17") - would this look through entire sheet.
myCell.Comments.Shape.Visible - what this line checks for?

Thanks.

Pike,
code you posted works fine within workbook. I'm actually reading comments from another workbook. I modified your code to read from another workbook, I got error at below line, seems like, myCell.Comments is null.

If myCell.Comments.Shape.Visible = True Then

Is there something I have to change in code?

Also, I'm getting error while closing workbook.

wkb.Close

is it possible to read comments without opening workbook?

code to read comments from another spreadsheet:
----------------------------------------------------------------------

Dim mycomment As Comment
   Dim mySht As Worksheet
   Dim inputFilePath As String
   Dim outFilePath As String
   inputFilePath = Cells(3, 4)
   outFilePath = Cells(4, 4)
   
   On Error GoTo OpenWorkBook_Err
   
   Application.EnableEvents = False
   Set wkb = Workbooks.Open(inputFilePath)
   Application.EnableEvents = True
   
   Open outFilePath For Output As #1
    
    For Each mySht In wkb.Worksheets
    
    Print #1, vbCrLf & mySht.Name & vbCrLf
      
    For Each myCell In wkb.Worksheets(mySht.Name).Range("E9:G17")
        
        If myCell.Comments.Shape.Visible = True Then
                        
            Print #1, "From " & myCell.Comment.Parent.Parent.Name _
                     & myCell.Comment.Parent.Address _
                     & " Comes the comment: " _
                     & myCell.Comment.Text _
                     & "  OneColumnNext:=" & myCell.Offset(0, 1).Value _
                     & "  TwoColsOver:=" & myCell.Offset(-2, 1).Value _
                     
        End If
     Next myCell
     
Next mySht

Close #1

wkb.Close

OpenWorkBook_Err:

Thank you.

try this

Worksheets(mySht.Name).Comments(1).Shape.Select

it comes from the vba help but I cant get in to work #@#$%
Thinking is that if you can select the cell you can find its address then the offset value

Not pretty but does the job

Sub test()
    For Each myCell In Range("E9:G17")
        On Error Resume Next
        If myCell.Comments.Shape.Visible = True Then
            MsgBox myCell.Comment.Text & "Offset cell Value:=" & myCell.Offset(0, -1).Value
        End If
    Next myCell
End Sub

Code tags, dude, can you add please code tags to you posts

Hi gsr

The

on error resume next

as The syntax will away be an error unless the cell contains a Comment

Can you first create and collate an array of all the workbook values then
print it to the txt file?
other wise it not a good option as it uses errors and will err on nearly every cell

you could add the values to current workbook sheet then print form it?

dude,
This works for me

Sub tester()
Dim mycomment As Comment
   Dim mySht As Worksheet
   Dim inputFilePath As String
   Dim outFilePath As String
   inputFilePath = ThisWorkbook.Path & "\scroller_example.xls"

   outFilePath = ThisWorkbook.Path & "\tester.txt"
   
   'On Error GoTo OpenWorkBook_Err
   
   Application.EnableEvents = False
   Set wkb = Workbooks.Open(inputFilePath)
   Application.EnableEvents = True
   
   Open outFilePath For Output As #1
    
    For Each mySht In wkb.Worksheets

    Print #1, vbCrLf & mySht.Name & vbCrLf
      
    For Each myCell In wkb.Worksheets(mySht.Name).Range("A1:J20")
        On Error Resume Next
        If myCell.Comments.Shape.Visible = True Then
                        
          Print #1, myCell.Comment.Text & " " & mycell.address

           
           
           ' Print #1, "From " & myCell.Comment.Parent.Parent.Name _
            '         & myCell.Comment.Parent.Address _
            '         & " Comes the comment: " _
            '         & myCell.Comment.Text _
            '         & "  OneColumnNext:=" & myCell.Offset(0, 1).Value _
             '        & "  TwoColsOver:=" & myCell.Offset(-2, 1).Value _

        End If
     Next myCell
     
Next mySht

Close #1

wkb.Close SaveChanges:=False

End Sub

This works for me

Sub test()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim destinationRange As Range
    Dim sourceCell As Range, sourceComment As Comment
    Dim commentStr As String, outRRay As Variant
    
    Set sourceSheet = ThisWorkbook.Sheets("Before")
    On Error GoTo Halt
    If sourceSheet.Comments.Count > 0 Then
        On Error GoTo 0
        
        Set destinationSheet = ThisWorkbook.Sheets.Add
        Set destinationRange = destinationSheet.Range("A1:D1")
        destinationRange.Value = Array("More info", "Size", "ID", "Name")
        
        For Each sourceComment In sourceSheet.Comments
            With sourceComment
                commentStr = .Text
                commentStr = Application.Substitute(Application.Substitute(commentStr, Chr(10), vbNullString), Chr(13), vbNullString)
                commentStr = Application.Substitute(commentStr, ",", "=")
                outRRay = Array(vbNullString, _
                    Split(Split(commentStr, ":")(1), "=")(1), _
                    Split(Split(commentStr, ":")(1), "=")(3), _
                    Split(Split(commentStr, ":")(1), "=")(5))
                outRRay(0) = sourceComment.Parent.End(xlToLeft).Text
                With .Parent
                    If .End(xlUp).Row > 1 Then
                        outRRay(0) = outRRay(0) & .End(xlUp).Text
                    End If
                End With
            End With
            destinationRange.EntireColumn.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 4) = outRRay
        Next sourceComment
    End If
Halt:
    On Error GoTo 0
End Sub