How to Insert Picture from URL Using VBA in Excel (2 Methods)

We’re going to show you 2 methods of Excel VBA insert picture from URL. To demonstrate our methods, we’ve picked a dataset with 3 columns: #, Image, and URL. We’re going to insert images into the blank column from our URL.

excel vba insert picture from url


Download Practice Workbook


2 Ways to Insert Picture from URL Using VBA in Excel

1. Using VBA to Insert Picture from URL in Excel

We’re going to insert pictures from the URL by using Excel VBA into the Image column.

Steps:

  • Firstly, from the Developer tab >>> select Visual Basic.

The Visual Basic window will open.

  • Secondly, from Insert >>> select Module.

excel vba insert picture from url

  • Thirdly, type the following code into the Module.
Sub URLPhotoInsert()
    Dim cShape As Shape
    Dim cRange As Range
    Dim cColumn As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xRange = ActiveSheet.Range("D5:D10")
    For Each cell In xRange
        cName = cell
        ActiveSheet.Pictures.Insert(cName).Select
        Set cShape = Selection.ShapeRange.item(1)
        If cShape Is Nothing Then GoTo line22
        cColumn = cell.Column - 1
        Set cRange = Cells(cell.Row, cColumn)
        With cShape
            .LockAspectRatio = msoTrue
            If .Width > cRange.Width Then .Width = cRange.Width * 3 / 4
            If .Height > cRange.Height Then .Height = cRange.Height * 3 / 4
            .Top = cRange.Top + (cRange.Height - .Height) / 2
            .Left = cRange.Left + (cRange.Width - .Width) / 2
        End With
line22:
        Set cShape = Nothing
        Range("D5").Select
    Next
    Application.ScreenUpdating = True
End Sub

VBA Code Breakdown

  • On Error Resume Next
    • If the code finds any runtime errors, it will go to the next statement.
  • Application.ScreenUpdating = False
    • We’re setting the ScreenUpdating property to False. This is done to speed up the macro execution speed.
  • Set xRange = ActiveSheet.Range(“D5:D10”)
    • We’re setting the URL range to D5:D10. Our URL is up to cell D7, but we’ve set it to demonstrate that it will work in the case of an empty cell. You need to change this for your data.

After that, we’re running a “for loop” within that URL cell range.

  •  cColumn = cell.Column – 1
    • We’re setting the picture to be inserted in the C column (before the image URL). You need to change it according to your need.

Here, if there is a blank cell then line22 will be executed. Then, we’re setting the picture properties in With cShape portion.

If the picture is too large than the cell size then the width and the height are also set to 75% of the cell size.

Then close the window.

  • After that, from the Developer tab >>> select Macros.

excel vba insert picture from url

The Macro dialog box will appear.

  • After that, select “URLPhotoInsert” as “Macro name:”.
  • Finally, click on Run.

The pictures will appear in the middle column. If you have a large number of picture URLs, it may take a longer time.

excel vba insert picture from url


Similar Readings


2. Using the Range.Offset Property to Insert Picture from URL

Here, we’ll insert pictures from the URL using Excel VBA in the cell range C5:C7. Here, we’ll use the Range.Offset property to choose the picture location in our sheet.

Steps:

Sub URLPhotoInsert2()
Dim cPhoto As String
Dim cPicture As Picture
Dim cRange As Range
Dim cItem As Range
Set cRange = Range("C5:C10")
For Each cItem In cRange
    cPhoto = cItem.Offset(0, -1)
    If cPhoto = "" Then Exit Sub
        Set cPicture = ActiveSheet.Pictures.Insert(cPhoto)
        With cPicture
            .ShapeRange.LockAspectRatio = msoFalse
            .Width = cItem.Width
            .Height = cItem.Height
            .Top = Rows(cItem.Row).Top
            .Left = Columns(cItem.Column).Left
            .Placement = xlMoveAndSize   
        End With 
    Next
End Sub

excel vba insert picture from url

VBA Code Breakdown

  • Set cRange = Range(“C5:C10”)
    • We’re setting the cell range to C5:C10. We’re adding empty cells to prove that our code works in the case of an empty cell. This is the cell range where the picture will be inserted. You need to change it for your dataset.
  • cPhoto = cItem.Offset(0, -1)
    • We’re telling this to obtain the picture URL from the B column.

We’re adjusting the picture properties using the With cPicture portion.

  • .ShapeRange.LockAspectRatio = msoFalse
    • We’re not locking the aspect ratio of the picture.
  • .Placement = xlMoveAndSize
    • This ensures the picture moves and resizes with the cell.
  • Save and close that window.
  • Thirdly, bring up the Macro window.
  • After that, choose “URLPhotoInsert2”.
  • Finally, click on Run.

We can see the pictures in column C. We’ve shown you another method to Excel VBA insert picture from URL.

excel vba insert picture from url

Read More: How to Insert an Image into a Cell with Excel VBA (2 Methods)


Practice Section

We’ve added data practice tables for your convenience in the Excel file. Moreover, remember you need to change the cell range in the code in order to insert pictures into the cells from the URLs.


Conclusion

We’ve shown you 2 methods to Excel VBA insert picture from URL. However, If you face any problems, feel free to comment below. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

6 Comments
  1. how to modify this code if i have merged range? as an example:
    Set cRange = Range(“C5:C7, C8:C10, C11:C13”)

    This code successfully retrieves the first value (URL) from the merged range, but the image position is only in the range C5, C8, and C11. What I want is the image position to be at C5:C7, C8:C10, C11:C13.

    • According to your example, Set cRange = Range(“C5, C8, C11”)
      Then, you’ll need to change the height & width to MergeArea
      .Width = cItem.MergeArea.Width
      .Height = cItem.MergeArea.Height

      Full Code >

      Sub URLPhotoInsert2()
      Dim cPhoto As String
      Dim cPicture As Picture
      Dim cRange As Range
      Dim cItem As Range
      Set cRange = Range("C5, C8, C11")
      For Each cItem In cRange
          cPhoto = cItem.Offset(0, -1)
          If cPhoto = "" Then Exit Sub
              Set cPicture = ActiveSheet.Pictures.Insert(cPhoto)
              With cPicture
                  .ShapeRange.LockAspectRatio = msoFalse
                  .Width = cItem.MergeArea.Width
                  .Height = cItem.MergeArea.Height
                  .Top = Rows(cItem.Row).Top
                  .Left = Columns(cItem.Column).Left
                  .Placement = xlMoveAndSize
              End With
          Next
      End Sub

      The output will be this: https://ibb.co/KXHVzSC

      • I want to send the file, but the images disappear when saving. How to save the loaded images in the file?

        • Thank you for your question.

          In my second code, instead of “ActiveSheet.Pictures.Insert” you can try using “ActiveSheet.Shapes.AddPicture”, which should embed the images in the Excel file and it will not disappear.

          the modified VBA code from the second method will be:

          Sub URLPhotoInsert2()
          
          Dim cPhoto As String
          Dim cPicture As Shape
          Dim cRange As Range
          Dim cItem As Range
          
          Set cRange = Range("C5:C10")
          For Each cItem In cRange
              cPhoto = cItem.Offset(0, -1)
              If cPhoto = "" Then Exit Sub
                  Set cPicture = ActiveSheet.Shapes.AddPicture(cPhoto, _
                  msoFalse, msoTrue, Columns(cItem.Column).Left, _
                  Rows(cItem.Row).Top, cItem.Width, cItem.Height)
                  
                  With cPicture
                      .LockAspectRatio = msoFalse
                      .Placement = xlMoveAndSize
                  End With
                  
              Next
          
          End Sub
  2. Does it work with images in Google Drive shared with those who have the link? I’m trying but I can’t.

    It also doesn’t work for me with the following image:
    https://www.exceleinfo.com/wp-content/uploads/2022/06/Referencias-3D-en-Excel-1024×477.png

    It works fine with this image:
    https://sea-eu.org/wp-content/themes/theme_seaeu_uca/images/logo/logo_seaeu4.png

    I do not know what I’m doing wrong

    • You need to change Google Drive’s Shared URL when inputting it into the cell.
      For example, if I share an image the URL will be -> “https://drive.google.com/file/d/18r34oAVY8-bicTmf3CaTIumuHp_hqZxH/view?usp=sharing”.
      Then, I need to change it to -> “https://drive.google.com/uc?export=view&id=18r34oAVY8-bicTmf3CaTIumuHp_hqZxH” (putting the values after d/ inside the id values of the URL).

      And for your second question, I think the URL is not correct, if I remove the image resolution info from the URL, then it works perfectly in my second code.

      Your URL -> “https://www.exceleinfo.com/wp-content/uploads/2022/06/Referencias-3D-en-Excel-1024×477.png”.
      it should be -> “https://www.exceleinfo.com/wp-content/uploads/2022/06/Referencias-3D-en-Excel.png”.

Leave a reply

ExcelDemy
Logo