CODEWITHSUNDEEP
excelimage
Serhito
Serhito

Reputation: 41

Excel loading image from url

I have this picture from a website that I am trying to insert into an excel spreadsheet. I need to modify part of the url with data from a cell (this case cell B2). This specific picture is working fine if I don't modify it, but if I do modify it, it doesn't work.

Here's what I have so far...

Sub Chart()
'
' Macro1 Macro
'

 Range("j3").Select
    ActiveSheet.Shapes.Range(Array("CHART1")).Select
    Selection.Delete
    
    
   
 _
        Dim Pict As Object

'// Note Inserts Picture Object @ Activecell and names it
Set Pict = ActiveSheet.Pictures.Insert("https://stockcharts.com/c-sc/sc?s=" & Range("b2") & "&p=D&b=5&g=0&i=0&r=1598561613350")
Pict.Name = "CHART1"
Range("d10").Select


End Sub

Does it have to do anything with the format of the picture itself ?

Thanks

Upvotes: 1

Views: 684

Answers (1)

Mech
Mech

Reputation: 4015

Just change the workbook and worksheet references and this should work for you. I've cleaned up the recorded macro and gave your ranges references to the workbook and worksheet that the data is on. Using ActiveSheet can be unpredictable.

Note that the value in B2 has to be a valid reference to the picture for it to work correctly.

Note that because those are not actual images (.jpg, .gif, etc) and are system generated, it took a little more work to get to work. This will make a copy of the site and paste it as an image.

Sub Chart()
    ' declare variable types - immutable                                    do not modify
    Dim WB As Workbook                                                      ' workbook reference    - full name of the .xlsm file
    Dim WS As Worksheet                                                     ' worksheet reference   - worksheet within workbook containing object
    Dim Pict As Object
    Dim imgLoc As String
    Dim DataObj As New MSForms.DataObject

    ' variables - mutable                                                   ok to modify
    Set WB = Workbooks("SO.xlsm")                                           ' set the name of the origin workbook here
    Set WS = WB.Worksheets("63624135")                                      ' set the name of the origin worksheet here
    
    imgURL = WS.Range("B2")                                                 ' URL of image
    imgLoc = "A1"                                                           ' cell location of the image
    
    ' with thanks to https://stackoverflow.com/a/43522345/4101210
    Set IE = CreateObject("InternetExplorer.Application")
    
    With IE
        .Visible = False
        .Navigate imgURL
        Do Until .ReadyState = 4: DoEvents: Loop
    End With

    IE.ExecWB 17, 0 '// SelectAll
    IE.ExecWB 12, 2 '// Copy selection

    IE.Quit
    
    ' with thanks to https://stackoverflow.com/a/41394617/4101210
    DataObj.GetFromClipboard
    On Error GoTo Img
        GetClipboardText = DataObj.GetText
    On Error GoTo 0
Img:
    If Err = -2147221404 Then
        Err = 0
        WS.Paste Destination:=WS.Range(imgLoc), Link:=False
    Else
        'do nothing
    End If
    
End Sub

Upvotes: 1

Related Questions