Insert image from URL into a spreadsheet

Question

Hello.

I have problems inserting images into an Excel sheet from a URL address.

I can of course use the formula =IMAGE(A1,,3,150) and the URL is in Cell A1.

The problem then becomes that the image itself is in the cell and can make the spreadsheet very difficult to store if there are many images.

I have previously used a macro that works if I point to a jpg file or similar, but it does not work on the URL address below.

https://asset-prod1a-euw.productmarketingcloud.com/api/assetstorage/67_157c61d4-73d4-498f-9be5-c8824aaa7a1a/NGThumb

While this one, on the other hand, works fine.

https://www.google.no/images/branding/googlelogo/1x/googlelogo_color_272x92dp.png

How do I solve this when the URL is put in column J as in this case?

Private Sub Test_1()

   Dim rCell As Range

    Dim newFileName As String

    Dim vntBilde As Variant

    Dim Svar As Integer

 

    Set rCell = Range("b10")

    Do While rCell.Row > 1

    If rCell.Value = "" Then Exit Do

    newFileName = rCell.Offset(0, 8).Range("a1")

    rCell.Offset(0, 1).Range("a1").Select

    On Error Resume Next

    ActiveSheet.Pictures.Insert(newFileName).Select

    Selection.ShapeRange.ScaleWidth 0.45, msoFalse, msoScaleFromTopLeft

    Selection.ShapeRange.ScaleHeight 0.45, msoFalse, msoScaleFromTopLeft

  

    rCell.Offset(1, 0).Range("a1").Select

    Set rCell = Selection(1, 1)

    rCell.Select

       

If rCell.Value = "" Then Exit Do

Loop

End Sub