Adding Comments

Paul is importing a text file into Excel. One of the fields of the text file is particularly long and he’d prefer if that field was made a comment instead of put in a cell. You certainly need VBA to accomplish this, but there are probably a couple of ways to tackle it.

You could import the text file normally, then run a macro to put some of the data into comments. Or you could access the data in the text file directly through VBA. I prefer the latter. Take this comma delimited text file, for example.

Notepad file showing comma delimited values

I can run this code to put the data in the cells or comments I want:

Sub TxtToComms()
   
    Dim lFnum As Long
    Dim sFname As String
    Dim i As Long
    Dim sInput As String
   
    sFname = “C:TesterImportComment.txt”
    lFnum = FreeFile
   
    Open sFname For Input As lFnum
   
    Do While Not EOF(lFnum)
        i = i + 1
        Input #lFnum, sInput
        Sheet1.Cells(i, 1).Value = sInput
       
        Input #lFnum, sInput
        Sheet1.Cells(i, 1).AddComment sInput
    Loop
   
End Sub

This simply puts the first field in the cell and uses the AddComment method to create a comment that holds the second field.

Excel range showing some text put into comments

Posted in Uncategorized

9 thoughts on “Adding Comments

  1. Thanks very much for putting this together, Dick. You just saved me and my associates a lot of work.

    You mention using a macro to transfer cell data into comments. I never did get that to work because the macro ended up with the data that went into the cell comment (during the record step) hard-coded in the macro, even though I was using cut and paste. Maybe it can be done by someone more skilled with Excel, or maybe I just need to update my copy.

    The above web site is where we will be using this fragment of code, for the 2005 session. Thanks again,

    Paul

  2. One point – it’s a good idea to close the file when you’re done with it. For anyone who doesn’t know, this is simplicity itself in VBA:

    ‘close all open files
    Close

    OK two points – this one even pickier: using AddComment on a cell that already has a comment throws an error, so possibly a good idea to clear the output cells before running.

    I don’t mean to be negative though – this is a good and very clear example of how to read from a file.

  3. Another approach using Line Input then using Split function.
    Split a function of Excel 2000 onward…

    Dim arr() As String

    Do Until EOF(lFnum)
    i = i + 1
    Line Input #lFnum, sInput
    arr = Split(sInput, “,”, 2)
    Sheet1.Cells(i, 1).Value = arr(0)
    Sheet1.Cells(i, 1).AddComment arr(1)
    Loop

    Close #lFnum

    Cheers,
    Rob

  4. Charlie: It’s pretty poor of me not to close the file. I thought about the error checking and didn’t use it because I assumed it would always go into a new workbook. At the very least I should have disclosed that assumption, but really I should have put error handling around that. Thanks for pointing those out.

  5. Rob: Do you have a preference? I use Input for two columns or less and Line Input for four columns or more. (Three columns depends on my mood.) Certainly if the setup is such that you can loop through the array created by Split, it makes sense to use it.

  6. Dick,

    I haven’t needed to recently.

    If I had to choose, I’d use Line Input – just because My preference is to control import processes rather than let a helper make the assumptions.

  7. hi, is there a way to add graph or a pic to the Comment using VBA ??? am abel to add a graph using excel, but will like to put it using VBA ?

    can neone help ?

    thanks

  8. Kos,

    Something like this.
    Note: requires an existing chartobject on the activesheet.
    Sub GraphComment()

    Dim cmtTemp As Comment
    Dim chtTemp As Chart
    Dim strFilename As String

    Set cmtTemp = ActiveCell.Comment
    If cmtTemp Is Nothing Then
    Set cmtTemp = ActiveCell.AddComment
    End If

    strFilename = ThisWorkbook.Path & “z” & _
    Format$(CLng(Rnd() * 100000), “000000?) & “.gif”

    Set chtTemp = ActiveSheet.ChartObjects(1).Chart
    chtTemp.Export strFilename

    cmtTemp.Shape.Fill.UserPicture strFilename

    Kill strFilename

    Set cmtTemp = Nothing
    Set chtTemp = Nothing

    End Sub

    Cheers
    Andy

  9. Have an excel spreadsheet connected to my stock account. The spread sheet receives DDE feed for prices, quantities etc.. Well long story short, I wanted to have a running total of 4 cells in excel as they received DDE data. I tried a private sub macro but had to put it into the VBA code for the sheet and it would only work for one cell, it is below:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range(“V72?)) Is Nothing Then Exit Sub
    With Range(“V82?)
    .value = .value + [V72]
    End With
    End Sub

    So I tried a macro from the Microsoft website which keeps a running total in the comment section of a cell. It works but when I use an ‘if’ statement to load the comment from the DDE fed cell to the cell with the comment macro, the value goes into the cell but not into the comment, and so no running total in the comment.

    My Question is this – Is there some way to make my macro above work with more than one cell? And if that is negative, is there a way to feed the data from the DDE cell to the cell with the running total going into the comment section? Any advice would be appreciated. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *