Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comments in Excel (2000)

    I have a woorkbook that has a long list of entries with comments, I am turning it into an Access database I need to extract the comments so that they are just the text in a field instead of the comments, do any of you guys know of any code that can do this.

    I used the following code to extract the hyperlinks that were in the document, with this you select a column and rund the macro and it extracts all the hyperlinks to the column on the right.

    Sub ExtractHL()
    Dim cel As Range

    With ActiveWindow.RangeSelection
    If .Columns.Count > 1 Then
    MsgBox "Select 1 column only. Next column must be empty!"
    Exit Sub
    End If

    For Each cel In .Columns(1).Cells
    If cel.Hyperlinks.Count > 0 Then
    cel(1, 2) = cel.Hyperlinks(1).Address
    End If
    Next
    End With
    End Sub

    any help would be greatly appreciated

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comments in Excel (2000)

    Welcome to Woody's Lounge!

    Try something like this:

    Sub SeparateComments()
    Dim oCell As Range
    Dim strComment As String
    Dim intPos As Integer
    For Each oCell In Selection.Columns(1)
    If Not oCell.Comment Is Nothing Then
    strComment = oCell.Comment.Text
    intPos = InStr(strComment, vbLf)
    oCell.Offset(0, 1) = Mid(oCell.Comment.Text, intPos + 1)
    End If
    Next oCell
    Set oCell = Nothing
    End Sub

  3. #3
    New Lounger
    Join Date
    Oct 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comments in Excel (2000)

    Hello HansV, thanks for the warm welcome and the quick reply, i tried running this as a macro but nothing seemed to happen. I selected the whole column, part of the column and just a single field but nothing happend when I ran the macro, there were no error messages though!

    Thanks

    Niblet

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comments in Excel (2000)

    Try changing

    For Each oCell In Selection.Columns(1)

    to

    For Each oCell In Selection

    The code does not check whether there is a blank column to the right of cells with comment.

  5. #5
    New Lounger
    Join Date
    Oct 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comments in Excel (2000)

    Thanks that has worked a treat!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •