URL to Picture in Cell (URL structure doesn't show it has a picture!)

[sinasdf]

I have an issue in terms of getting a URL link to show the picture in the adjacent column.

Example:
https://dyn.keepa.com/pricehistory.p...d&cUsed=ffffff

I've used the coding for turning url images into the adjacent column, as indicated in the link here:
https://www.extendoffice.com/documen...-from-url.html

However, it seems that the way the URL is structured (the image format code is in the middle of the URL rather than end) makes it difficult for VBA to recognize it as an image

Does anyone know how to account for this?

 

[Kenneth Hobson]

Normally, one wants all the pics to be the same size for multiple imports. I like to preset my column width and row heights but that can be done in the macro.

This will make it easier for others to test.

Sub Setup()
  [A2] = "https://www.extendoffice.com/images/stories/new%20box/KTE_125x125.png"
  [A3] = "https://www.extendoffice.com/images/stories/new%20box/box_Office_tab_125_125.png"
  [A4] = "https://www.extendoffice.com/images/stories/box/kutools-outlook-125x125-tm.png"
  [A5] = "https://www.extendoffice.com/images/stories/box/box-kutools-word-125x125.png"
  '[A6] = "https://www.extendoffice.com/images/Box2010_Office_ProPlus_125_125.png"
  [A6] = "https://dyn.keepa.com/pricehistory.png?asin=B001GZ6QEC&domain=com&" & _
    "width=800&height=250&amazon=1&new=1&used=1&salesrank=1&range=31&c" & _
    "Background=000000&cFont=cdcdcd&cAmazon=ffba63&cNew=8888dd&cUsed=ffffff"
End Sub

'https://www.extendoffice.com/documents/excel/4212-excel-insert-image-from-url.html?page_comment=2
Sub URLPictureInsert()
'Updateby Extendoffice 20161116
  Dim Pshp As Shape, Rng As Range, cell As Range, xRg As Range
  Dim xCol As Long, filenam As String
  
  On Error Resume Next
  Application.ScreenUpdating = False
  
  Set Rng = ActiveSheet.Range("A2:A6")
  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)
    'Preset, Font Calibri 11 point, column width 18.86, row height 102.
    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("A2").Select
  Next cell
  
  Application.ScreenUpdating = True
End Sub

 

[sinasdf]

Hi Kenneth,

Thanks for the expanded tips!

My main concern though is that the last link, the keepa link doesn't return an image (probably because of how the URL is built)

Is there any workaround this?

Here is a simpler link:
https://dyn.keepa.com/pricehistory.png?asin=B001GZ6QEC&domain=com&" & _

 

[Kenneth Hobson]

It worked fine for me. I modified the 2nd sub to iterate this last one as well.

Sub Setup()
  [A2] = "https://www.extendoffice.com/images/stories/new%20box/KTE_125x125.png"
  [A3] = "https://www.extendoffice.com/images/stories/new%20box/box_Office_tab_125_125.png"
  [A4] = "https://www.extendoffice.com/images/stories/box/kutools-outlook-125x125-tm.png"
  [A5] = "https://www.extendoffice.com/images/stories/box/box-kutools-word-125x125.png"
  '[A6] = "https://www.extendoffice.com/images/Box2010_Office_ProPlus_125_125.png"
  [A6] = "https://dyn.keepa.com/pricehistory.png?asin=B001GZ6QEC&domain=com&" & _
    "width=800&height=250&amazon=1&new=1&used=1&salesrank=1&range=31&c" & _
    "Background=000000&cFont=cdcdcd&cAmazon=ffba63&cNew=8888dd&cUsed=ffffff"
  [A7] = "https://www.mrexcel.com/forum/redirect-to/?redirect=" & _
    "https%3A%2F%2Fdyn.keepa.com%2Fpricehistory.png" & _
    "%3Fasin%3DB001GZ6QEC%26domain%3Dcom%26%2522%2520%26%2520_ "
End Sub

 

[sinasdf]

It worked fine for me. I modified the 2nd sub to iterate this last one as well.

Sub Setup()
  [A2] = "https://www.extendoffice.com/images/stories/new%20box/KTE_125x125.png"
  [A3] = "https://www.extendoffice.com/images/stories/new%20box/box_Office_tab_125_125.png"
  [A4] = "https://www.extendoffice.com/images/stories/box/kutools-outlook-125x125-tm.png"
  [A5] = "https://www.extendoffice.com/images/stories/box/box-kutools-word-125x125.png"
  '[A6] = "https://www.extendoffice.com/images/Box2010_Office_ProPlus_125_125.png"
  [A6] = "https://dyn.keepa.com/pricehistory.png?asin=B001GZ6QEC&domain=com&" & _
    "width=800&height=250&amazon=1&new=1&used=1&salesrank=1&range=31&c" & _
    "Background=000000&cFont=cdcdcd&cAmazon=ffba63&cNew=8888dd&cUsed=ffffff"
  [A7] = "https://www.mrexcel.com/forum/redirect-to/?redirect=" & _
    "https%3A%2F%2Fdyn.keepa.com%2Fpricehistory.png" & _
    "%3Fasin%3DB001GZ6QEC%26domain%3Dcom%26%2522%2520%26%2520_ "
End Sub

It works great! I appreciate your help Kenneth

On a related question..do you know how I can easily remove the images from the column (without using VBA)

I will have several hundred images being exported and I have to make some changes - deleting the column doesn't remove

 

[Kenneth Hobson]

Macro method is best.

You could Ctrl+left click each image then press Delete key.

If all objects on a sheet, the ribbon method:
1. Go to “Find & Select” in the Home ribbon (far right)
2. Click on “Go To Special”
3. Select “Objects”
4. Click “OK”