Help needed with VBA to auto insert pic into comment

[Dyjoab]

Pretty sure everyone is going to jump on me and say "there is already a thread for that!"
BUT I have been through just about every forum etc written in the last million (at least) years, and can't find one that works for me.


So here goes:

I am using Excel 2010.


I have in column A a product code. This starts at row 4 and goes down to about 2000.
I would like to automatically create a comment for each product code with a corresponding jpeg file stored in a sub folder of the following: C:\Users\lisa\Desktop\images (I have several folders inside of this one for each different supplier). The file name corresponds directly with the product code but with .jpg at the end. My Excel document can have up to a couple of thousand rows at any given time.


Should also say that not every image will be required every time. So I need it to link only to the required image in column A, not all. I also do not want any text in the comment, just the pic only.


I am only really, really new at VBA's, so go gently please


Thanks in advance

[snb]

Sub M_snb()
  With Application.FileDialog(3)
    .Filters.Add "Images", "*.jpg"
    If .Show Then Sheet1.Cells(12, 3).AddComment.Shape.Fill.UserPicture .SelectedItems(1)
  End With
End Sub

[Kenneth Hobs]

Welcome to the forum!

I don't know how to deal with subfolders if you don't have it listed in a cell.

This method illustrates a concept where it allows text plus a picture. The text can be passed as "".

It is fairly trivial to iterate a range or even let the macro set the range.

Something you need to consider is what size you want the image if you use my method.

First, see if this or snb's method works for you.

Sub Test_CommentInsertImageText()  
  CommentInsertImageText Range("A1"), _
    "U:\Material\ASPHALT\DESIGN\pics\5-25-2010 NCAT Track\dcp_1936.jpg", _
    "NCAT" & vbLf & "Auburn, AL", 2, 4
End Sub


Sub CommentInsertImageText(aCell As Range, sPath As String, sText As String, _
  Optional inHeight As Double = 0, Optional inWidth As Double = 0)
  
  Dim c As Comment, p As StdPicture
  
  If Len(Dir(sPath)) = 0 Then Exit Sub
  
  On Error Resume Next
  Set c = aCell.Comment
  c.Delete
  On Error GoTo 0
  
  Set p = LoadPicture(sPath)
  aCell.AddComment
  Set c = aCell.Comment
  With c.Shape
    If inHeight = 0 Then
      .Height = p.Height / 25.4
      Else
      .Height = inHeight * 96
    End If
    If inWidth = 0 Then
      .Width = p.Width / 25.4
      Else
      .Width = inWidth * 96
    End If
    .Fill.UserPicture sPath
  End With
  c.Shape.TextFrame.Characters.Text = sText
End Sub

[Dyjoab]

Hi Snb.
Thank you so much for your reply.
I ran your suggestion, but it just takes me to select the file that I want to insert in the comment.
Was hoping that it could do that automatically once I run it. Especially as there will be a lot!
Any suggestions? Am I doing something wrong maybe?

[Dyjoab]

Thanks so much for your reply Kenneth.
As I mentioned, I am very new to VBA's.
Can you tell me do I need to enter the path to my Images folder in this?
If so, where?
I am so confused...

[Kenneth Hobs]

Obviously, the routines would go in a Module.

The test routine is as simple as I can make it. Just change the path to suit and set the 2nd parameter value passed as "".

This is more complicated but I show how to set the range as the selection or letting the macro set A2 to last cell in A with a value.

Sub Main()  
  Dim c As Range, r As Range, fp As String
  
  fp = "C:\Users\lisa\Desktop\images\"
  'fp = "c:\myfiles\excel\pics\"
  'Set r = Range("A2", Range("A" & Rows.Count).End(xlUp))
  Set r = Selection
  
  For Each c In r
    CommentInsertImageText c, fp & c.Value & ".jpg", "", 2, 4
  Next c
End Sub

[Dyjoab]

So, I have run a few trials today with pretty much no success.
Sorry Kenneth, but my limited knowledge is not enough to get yours to work!

I found the following and gave it a go, and it places a comment next to each file name (in column B, but I can work with that).
However, the image is not in the comment, it is blank. No text, which is great, but no image either!
Any ideas?

Option Explicit
' Developed by Contextures Inc.
' contextures
Sub InsertComment()
Dim rngList As Range
Dim c As Range
Dim cmt As Comment
Dim strPic As String

On Error Resume Next


'change this to the range that contains the file names in your workbook
Set rngList = Range("A4:A2000")


'change this to the folder path for your picture files
strPic = "C:\users\lisa\desktop\images"


If Right(strPic, 1) <> "\" Then
strPic = strPic & "\"
End If


For Each c In rngList
With c.Offset(0, 1)
Set cmt = .Comment
If cmt Is Nothing Then
Set cmt = .AddComment
End If
With cmt
.Text Text:=""
.Shape.Fill.UserPicture strPic & c.Value
.Visible = False
End With
End With
Next c


End Sub

Thank you so much for your help, it is greatly appreciated.

[snb]

I think you'd better master the fundamentals of VBA first.

[Kenneth Hobs]

.Shape.Fill.UserPicture strPic & c.Value & ".jpg"

I hope that you are lisa as only lisa or an admin could access her desktop. Obviously, the jpg file must exist.

[snb]

And please use code tags in this forum.

[Kenneth Hobs]

Insert code tags by typing or clicking the # icon on the reply toolbar. Paste code between tags.