How to name extracted images with a specific cell content?

Question

I import product images from a database into Excel using the code below:

Sub URLPictureInsert() 
'Updateby Extendoffice 20161116 
    Dim Pshp As Shape 
    Dim xRg As Range 
    Dim xCol As Long 
    On Error Resume Next 
    Application.ScreenUpdating = False 
    Set Rng = ActiveSheet.Range("A1:A500") 
    For Each cell In Rng 
       filenam = cell 
       ActiveSheet.Pictures.Insert(filenam).Select 
       Set Pshp = Selection.ShapeRange.Item(1) 
       If Pshp Is Nothing Then GoTo lab 
       xCol = cell.Column + 1 
       Set xRg = Cells(cell.Row, xCol) 
       With Pshp 
          .LockAspectRatio = msoFalse 
          .Width = 100 
          .Height = 100 
          .Top = xRg.Top + (xRg.Height - .Height) / 2 
          .Left = xRg.Left + (xRg.Width - .Width) / 2 
    End With 
lab: 
   Set Pshp = Nothing 
   Range("A1").Select 
   Next 
   Application.ScreenUpdating = True 
End Sub

I have to download 100's of images at a time and this script names all the images "image 1" and so on to 500 at times.

How can I get the code to name each image with the product code in the cell next to it?

Essentially I have the address of the image in column A1...A500 and the corresponding product code in B1...B500. I want it to find the image in A1 and name it the code in B1 and not "image 1".

First: [Avoid using Select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Second: Something like `Cell,Offset(0,1).Value` should give you the product code corresponding to the current `Cell` — cybernetic.nomad, Dec 03 '19 at 21:57
do you mean, the name of the image in the spreadsheet (which is generally invisible?) or rename the file that you just imported? — RowanC, Dec 03 '19 at 22:26

score 0 · Answer 1 · answered Dec 03 '19 at 23:03

0

For naming the image so that it appears in range / sheets:

Option Explicit
Sub URLPictureInsert()

    Dim xRg As Range
    Dim xCol As Long
    Dim rng As Range
    Dim cell As Variant
    Dim filenam As String
    Dim myImg As Picture
    Application.ScreenUpdating = False
    Set rng = ActiveSheet.Range("A1:A500")
    For Each cell In rng
       filenam = cell.Value
       Set myImg = ActiveSheet.Pictures.Insert(filenam)
       If Not myImg Is Nothing Then
           xCol = cell.Column + 1
           Set xRg = Cells(cell.Row, xCol)
           With myImg
              '.LockAspectRatio = msoFalse
              .Width = 100
              .Height = 100
              .Top = xRg.Top + (xRg.Height - .Height) / 2
              .Left = xRg.Left + (xRg.Width - .Width) / 2
            End With
        End If
        'this line names the image by taking the value in the cell to the right
        myImg.Name = cell.Offset(0, 1).Value
        Set myImg = Nothing
        Range("A1").Select
   Next
   Application.ScreenUpdating = True
End Sub

answered Dec 03 '19 at 23:03

RowanC

1,611
10
16

Hi Rowan, thanks for looking at this. So column A has the image URL and column B has the Product code. The result of A should be named B instead of the generic "image#" – Gman Dec 05 '19 at 13:32
Plus, I've noticed that your script downloads the images twice...? – Gman Dec 05 '19 at 15:56
ok, the code above only imports the image once, but it will do so again if the code is run again? when you say 'the result of A' do you mean the image itself, it's range name, a caption, or something different. The above code currently gives the image a name with the value in B. However, this name is generally invisible - it's the range name/shapename. – RowanC Dec 06 '19 at 22:57
Ok, I think I see what you mean. I download the actual image, then save the excel as an .htm giving me two files, one the .htm with the addresses and the other an .fdl with all the images. In this file, I need the images to have the relevant product code as the image name instead of "image#". I'm assuming that this process starts in the initial download and naming script. Again thanks a lot for looking at this! – Gman Dec 09 '19 at 10:27
I guess my other question would be ... Could this issue have anything to do with the fact that I'm running excel of Mac? – Gman Dec 09 '19 at 11:30

How to name extracted images with a specific cell content?

1 Answers1