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.
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
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.
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
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.
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
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.
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.
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.
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
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
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