VBA auto-add image to cell from folder

[menno_edeltrend]

Dear mrExcellers,

In my company we work with a product database with calculations in it, every product offcourse has an unique article number. In the shared server we have a folder with pictures of every product with the article number as the title. We want to add the pictures automatically to every product with VBA, I know it is possible but my knowledge doesn't stretch far enough.

The article number stands in column D, the picture has to been placed in column B. The cel widht and hight are minimal, it would be nice to have the picture expending when you move the mouse over it.

I would like to let VBA start in the selected row and then go on from there till there is no article number in column D. That way it doesn't overwrite all the previous uploaded pictures.

I hope there is someone out there with the solution, thanks in advance!

 

[menno_edeltrend]

No one with an idea

 

[DanteAmor]

Try this to insert images.

Change folder for your folder name. In the cell you must put the name of the file and the extension. ex: art123.jpg

Sub Auto_Add_Image()
  Dim i As Long, wPath As String, wFile As String, sh As Worksheet
  If ActiveCell.Column <> 4 Then
    MsgBox "Select column D"
    Exit Sub
  End If
  If ActiveCell.Value = "" Then
    MsgBox "Select article number in column D"
    Exit Sub
  End If
  Set sh = ActiveSheet
  wPath = [COLOR=#ff0000]"C:\trabajo\images\"[/COLOR]
  For i = ActiveCell.Row To Range("D" & Rows.Count).End(xlUp).Row
    wFile = wPath & Range("D" & i)
    With sh.Pictures.Insert(wFile)
        .ShapeRange.LockAspectRatio = msoFalse
        .Top = Range("B" & i).Top + 1
        .Left = Range("B" & i).Left + 1
        .Width = Range("B" & i).Width - 2
        .Height = Range("B" & i).Height - 2
    End With
  Next
End Sub

 

[menno_edeltrend]

DanteAmor,

Thanks for yu reply! Unfortunately the code doesn't work. It gives a fault in the "With sh.Pictures.Insert(wFile)" line, do you have a solution for this? Also I would like to be able to choose the folder I want to use manually. I hope to hear from you!

 

[DanteAmor]

What does the error message say?


The name in the cell and in the folder must be identical.

In the cell you must put the name of the file and the extension. ex: art123.jpg

Try this please.

Sub Auto_Add_Image()
  Dim i As Long, wPath As String, wFile As String, Sh As Worksheet
  If ActiveCell.Column <> 4 Then
    MsgBox "Select column D"
    Exit Sub
  End If
  If ActiveCell.Value = "" Then
    MsgBox "Select article number in column D"
    Exit Sub
  End If
  Set Sh = ActiveSheet
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Selecciona una carpeta"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path & "\"
    If .Show <> -1 Then Exit Sub
    wPath = .SelectedItems(1) & "\"
  End With
  For i = ActiveCell.Row To Range("D" & Rows.Count).End(xlUp).Row
    wFile = wPath & Range("D" & i)
    If Dir(wFile) <> "" Then
      With Sh.Pictures.Insert(wFile)
          .ShapeRange.LockAspectRatio = msoFalse
          .Top = Range("B" & i).Top + 1
          .Left = Range("B" & i).Left + 1
          .Width = Range("B" & i).Width - 2
          .Height = Range("B" & i).Height - 2
      End With
    Else
      MsgBox "The file does not exist: " & wFile
    End If
  Next
End Sub

 

[menno_edeltrend]

I think I got it, thank you a lot!

 

[menno_edeltrend]

Unfortunately it doesn't work, the code now sets the folder that I select as the .jpg, while I want the code to search in the folder for the right combination of article number.jpg. I think the solution is very close but I can't figure it out.

 

[Dan_W]

Unfortunately it doesn't work, the code now sets the folder that I select as the .jpg, while I want the code to search in the folder for the right combination of article number.jpg. I think the solution is very close but I can't figure it out.

The code provided to you sets the path of the JPG files (wPath), and then completes the filename (wFile) by sourcing the information from Column D. If the names in Column D don't match what's on your computer exactly it won't work, but that isn't an issue with the code.

One way of checking might be to get a list of all the files in the directory and copy them across as-is; remove the path info; and then run the code.

 

[menno_edeltrend]

It worked with the first code with a fixed folder instead of the folder selection like in the second code, to the filenames should be correct. The code now says somethng like folder_name.jpg doesn't exist, t doesn't say that article_number.jpg doesn't exist. So it sets the folder as the image instead of the image in the folder.

 

[DanteAmor]

For the second code.Check the following:

------------------------------