Paste URLs as Images in Excel with VBA

 In this blog, we will create an Excel VBA code that will turn any image URL into actual images in Excel. 

Here is the full code : We will break down the code to undestand how it works.

Sub URLPictureInsert

    Dim rng As Range

    Dim cell As Range

    Dim Filename As String

    Dim theShape As Shape

    Dim xRg As Range

    Dim xCol As Long, k As Long

    On Error Resume Next

    Application.ScreenUpdating = False

   

    Set rng = Application.InputBox("Select the cells with hyperlinks.", , , , , , , 8)

   k = Application.InputBox("What should be the column difference between links and

Result column? Example: Links in Column C (3.column), result column E (5.column) then

you should type 2 here. ", , , , , , , 1)

    For Each cell In rng

            Filename = cell

                If InStr(UCase(Filename), "JPG") > 0 Or

                   InStr(UCase(Filename), "PNG") > 0 Or

                       InStr(UCase(Filename), "JPEG") > 0 Then

                        ActiveSheet.Pictures.Insert(Filename).Select

                        Set theShape = Selection.ShapeRange.item(1)

        If theShape Is Nothing Then GoTo isnill

             xCol = cell.Column + k

            Set xRg = Cells(cell.Row, xCol)

                With theShape

                    .LockAspectRatio = msoTrue

                    .Width = 200

                    .Height = 200

                    .Top = xRg.Top

                    .Left = xRg.Left

                End With

            xRg.RowHeight = 210

            xRg.ColumnWidth = 40

isnill:

            Set theShape = Nothing

           

        End If

    Next

    Application.ScreenUpdating = True

    MsgBox ("Images pasted successfully.")

End Sub

   First of all, we named the macro as URLPictureInsert  and defined the necessary variables, using Dim keyword. 

   Then we created a range variable called rng that gets the value from the selection of the user. So if user selects the cells with URLs in it, we will be able to set our range variable with that selection.

   Then we created a variable called k to let user decide which cell are we going to paste the images. 

   After that, we have created a For loop that checks whether the URLs contains JPEG,JPG or PNG in the string.

   If nothing found, the loop will not paste anything for that URL. However, if the condition above is met, then we are going to paste that image to the user specified column and the active row.

   We can set some of the features of the image, like width, height, AspectRatio etc. depending on the needs. We can also set the column and row width and height of the cells that contains images. 

   After going through each URL from the user selection, loop will be finished with a success message. 

Note: This code will work on URLs with JPEG, JPG,PNG  URLs. However, you can add other conditions to the if the statement

   Now you can use this code snippet on any of your files. Just copy this code and paste it on the file you want.

   Also please keep in mind that, once you have applied these macros, Excel will ask you to save your file as "XLSM" ( macro enabled). There will be no change in your file and data in it. It will be just macro enabled version of your file.

If you liked this project, please don't forget to share and leave a comment below.

 

Share: