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 =PICTURE(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 

(The URL is not a direct link to an image file, but is presumably an API endpoint that returns a JSON object containing information about the image. How can I solve this with VBA?)

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

https://www.google.no/images/branding/googlelogo/1x/googlelogo\_color\_272x92dp.png

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

Answer 1

try this formula

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

alternatively，dynamic create a html page and copy from html table

http://e.anyoupin.cn/EData/?p=Tools/DisTbl&find=select+%27<img+src%3D%22%27%7C%7CURL%7C%7C%27%22>%27+images+from+urlsToImages%3B

Answer 2

The situation you mentioned is related to VBA code, you can refer to this article: Office VBA support and feedback | Microsoft Docs to go to Stack Overflow by using the VBA tag

There are also many experienced engineers and experts in the forums there.