Extracting comments from cells in Excel using VB6 4

[CruiseMan]

Using VB6, I have loaded an Excel worksheet object (I am using Excel 2000). I am having much difficulty in extracting the comments that are embedded in various cells within the worksheet. I have also tried using a Range object to get at the comment property....but alas, no cigar.

Any suggestions would be appreciated.

<...References set to Excel Object library>

Dim strText As String
Dim myRange As Range
Set myRange = Range("A70")
myRange.Offset(0, 9).Select
strText = myRange.Comment

 

[SkipVought]

Hi,

Check out the Comments Collection of the Worksheet Object. You can use the Next and Previous Method to navigate the comments on the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hypetia]

>I have also tried using a Range object to get at the comment property...
>Set myRange = Range("A70")

Where do you initialize the Range object? You have to obtain the reference to your Range object first.

See the following example.
___
[tt]
Private Sub Form_Load()
Dim Excel As Object, WB As Object, Sheet As Object
Set Excel = CreateObject("Excel.Application")
Set WB = Excel.Workbooks.Open("C:\Book1.xls")
Set Sheet = WB.Sheets(1)
MsgBox Sheet.Range("A1").Comment.Text
WB.Close
Excel.Quit
Unload Me
End Sub[/tt]

 

[TysonLPrice]

I added a reference to Excel in the project.

Option Explicit
Private xl  As Excel.Application
Private xlWorkBook As Excel.Workbook
Private xlWorksheet As Excel.Worksheet
Private Sub Form_Load()
    
    Dim strTestIt As String
    Dim strExcelRange As String
    
    Set xl = New Excel.Application
    Set xlWorkBook = xl.Workbooks.Add
    xl.Visible = True
    
    strTestIt = "Tek-Tips"
    strExcelRange = "A" & 1 & ":A" & 1
    Set xlWorksheet = xl.Sheets(1)
    xlWorksheet.Activate
    With xlWorksheet
        .Range(strExcelRange).Select
        .Range(strExcelRange).AddComment
        .Range(strExcelRange).Comment.Visible = True
        .Range(strExcelRange).Comment.Text Text:=strTestIt
        MsgBox .Range(strExcelRange).Comment.Text
    End With


End Sub

 

[CruiseMan]

Thanks for all the input. It was a big help. The only sidebar I have is this. If you attempt to read a comment from a cell that does not have a comment, an error 91 - "Object variable or With block variable not set." is thrown (see code).

Dim xlX7R_WS As Excel.Worksheet
Dim strCell As String
Dim strComment As String

xlX7R_WS.Activate

strCell = "A70"
error-> strComment = xlX7R_WS.Range(strCell).Comment.Text

I would have thought it would return a Null value. Any thoughts?

Thanks again!

 

[MakeItSo]

You can easily cycle through all comments rather than through all cells...

Sub test()
Dim cmt As Comment

For Each cmt In ActiveSheet.Comments
    MsgBox cmt.Text
Next cmt

End Sub

;-)

Cheers,
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

P.S: That's excatly what Skip proposed in post #2.


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[Hypetia]

>Any thoughts?

It is suggested that instead of reading the Text property directly, you first get a reference to the Comment object, and check beforehand if a comment is present. This would prevent error 91 which occurs when a data member of an uninitialized object is accessed.
___
[tt]
Dim xlX7R_WS As Excel.Worksheet
Dim strCell As String
Dim objComment As Object
Dim strComment As String

xlX7R_WS.Activate
strCell = "A70"
objComment = xlX7R_WS.Range(strCell).Comment
If objComment Is Nothing Then
MsgBox "No comments!"
Else
strComment = objComment.Text
End If[/tt]