Alignment of cells with pics

[ferocious12]

Hi All,

I am using the following code to copy images from a website (amazon in this case) based on URL of the images in column A. I am not sure how can I change the code so the picture related to each cell in column A appear in corresponding cell in column B. Currently, all the pics are pasted in one cell.

I have attached a sample

thanks for the help !

Function URLExists(sURL As String) As Boolean

    Dim XMLReq As Object
    
    On Error GoTo ErrHandler
    
    Set XMLReq = CreateObject("MSXML2.XMLHTTP")
    With XMLReq
        .Open "GET", sURL, False
        .Send
        URLExists = (.Status = 200)
    End With
    
ErrHandler:
    Set XMLReq = Nothing
    
End Function

'http://www.vbaexpress.com/kb/getarticle.php?kb_id=799

Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
    Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
    
     'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
    oXMLHTTP.Send 'send request
    
     'Wait for request to finish
    Do While oXMLHTTP.readyState <> 4
        DoEvents
    Loop
    
    oResp = oXMLHTTP.ResponseBody 'Returns the results as a byte array
    
     'Create local file and save results to it
    vFF = FreeFile
    If Dir(vLocalFile) <> "" Then Kill vLocalFile
    Open vLocalFile For Binary As #vFF
    Put #vFF, , oResp
    Close #vFF
    
     'Clear memory
    Set oXMLHTTP = Nothing
End Function

Sub URLPictureInsert()
    Dim ws As Worksheet
    Dim rRange As Range
    Dim rCell As Range
    Dim sURL As String
    Dim sTempFile As String
    Dim LastRow As Long
    Application.ScreenUpdating = False
    Set ws = Worksheets("URL Report")
    With ws
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rRange = .Range("A1:A" & LastRow)
    End With
    For Each rCell In rRange
        If Len(rCell) > 0 Then
            sURL = rCell.Value
            If URLExists(sURL) Then
                sTempFile = Environ("temp") & "\" & Mid(sURL, InStrRev(sURL, "/") + 1)
                SaveWebFile sURL, sTempFile
                ws.Shapes.AddPicture _
                    Filename:=sTempFile, _
                    LinkToFile:=msoFalse, _
                    SaveWithDocument:=msoTrue, _
                    Left:=rCell.Offset(, 1).Left, _
                    Top:=rCell.Top, _
                    Width:=100, _
                    Height:=100
                    Kill sTempFile
            Else
                rCell.Offset(, 1).Value = "File not found" 'optional
            End If
        End If
    Next rCell
    Application.ScreenUpdating = True
End Sub

 

[chirayu]

you can refer to below links. Basically the placement of images is specified by .Top and .Left, so what you would do is store the values as a variable in a loop so it iterates over the range and knows where to paste the image

How to insert a picture into Excel at a specified cell position with VBA

I'm adding ".jpg" files to my Excel sheet with the code below : 'Add picture to excel xlApp.Cells(i, 20).Select xlApp.ActiveSheet.Pictures.Insert(picPath).Select 'Calgulate new picture size With x...

stackoverflow.com

Inserting an Online Picture to Excel with VBA

I'm currently working on a project and need to fill in cells with pictures via URLs. All URLs are in one column, and I'd like to load the images in an adjacent column. I'm no VBA expert, but I foun...

stackoverflow.com

 

[ferocious12]

you can refer to below links. Basically the placement of images is specified by .Top and .Left, so what you would do is store the values as a variable in a loop so it iterates over the range and knows where to paste the image

How to insert a picture into Excel at a specified cell position with VBA

I'm adding ".jpg" files to my Excel sheet with the code below : 'Add picture to excel xlApp.Cells(i, 20).Select xlApp.ActiveSheet.Pictures.Insert(picPath).Select 'Calgulate new picture size With x...

stackoverflow.com

Inserting an Online Picture to Excel with VBA

I'm currently working on a project and need to fill in cells with pictures via URLs. All URLs are in one column, and I'd like to load the images in an adjacent column. I'm no VBA expert, but I foun...

stackoverflow.com

Thanks. One more thing if you can help. Above code is working with Amazon images but not with Flipkart Images links (says file not found). Any idea why ?