0

I am trying to extract an image from a URL and embed it in Excel.

My Excel Sheet is simple: it contains 2 columns.

Column 1 has the image URL. In column 2 I want to embed the image. I am using the following code. It is working perfectly fine for first row where I have saved the image on my local machine and given the path, but it fails when trying to embed straight from the URL. I receive the following error:

Error - Run time 1004 , unable to get the insert property for the picture class.

My code:

Sub Button1_Click()

'ActiveSheet.Pictures.Insert("C:\810CfHBPGyL._SX425_.jpg").Select

'Updateby Extendoffice 20161116

    Dim Pshp As Shape
    Dim xRg As Range
    Dim xCol As Long

    'On Error Resume Next

    Application.ScreenUpdating = False

    Set Rng = ActiveSheet.Range("A2:A3")

    MsgBox "Step1"

    For Each cell In Rng

        filenam = cell
        MsgBox "Step2" & cell
        ActiveSheet.Pictures.Insert(filenam).Select
        MsgBox "Step3"

        Set Pshp = Selection.ShapeRange.Item(1)

        'MsgBox "Step4" & Pshp

        If Pshp Is Nothing Then GoTo lab
        xCol = cell.Column + 1
        Set xRg = Cells(cell.Row, xCol)

        With Pshp

            .LockAspectRatio = msoFalse
            .Width = 80
            .Height = 80
            .Top = xRg.Top + (xRg.Height - .Height) / 2
            .Left = xRg.Left + (xRg.Width - .Width) / 2

        End With

lab:

    Set Pshp = Nothing

    Range("A2").Select

    Next

    Application.ScreenUpdating = True

End Sub

Could this be a problem with the Excel VBA references?

braX
  • 11,506
  • 5
  • 20
  • 33
Nishant Aggarwal
  • 1
  • Please use propper formatting for your code. On what line does the error occur? – Luuklag Dec 09 '18 at 18:33
  • Also read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba to improve your code. – Luuklag Dec 09 '18 at 18:34
  • The error occurs at line "ActiveSheet.Pictures.Insert(filenam).Select" – Nishant Aggarwal Dec 09 '18 at 18:42
  • You don't have `filenam` not declared (Dim). Is the directory on top the source directory? If so, there are two points (".") in it. Maybe it's somehow related to that. – prextor Dec 10 '18 at 14:01

0 Answers0