Excel macro (VBA script) to download images based on the URL specified in the cells

excel-download-images-from-url

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
   "URLDownloadToFileA" (ByVal pCaller As Long, _
   ByVal szURL As String, _
   ByVal szFileName As String, _
   ByVal dwReserved As Long, _
   ByVal lpfnCB As Long) As Long

Public Sub GURoL(url As String, FileName As String)
    Dim lngRetVal As Long
    lngRetVal = URLDownloadToFile(0, url, FileName, 0, 0)
    If lngRetVal <> 0 Then
    MsgBox "GURol godo: Can't download from " & url & " to " & FileName
    End If
End Sub

Sub a()
    Dim url_column As Range
    Dim image_column As Range
    Dim myPicture As Picture 'embedded pic
    Dim hasImage_column As Range
    Dim hasImage As String
    Dim shape As shape
    Dim localImagePath As String
    
    Dim fromRow As Integer
    Dim toRow As Integer
    
    fromRow = 2
    toRow = 10
    
    Dim urlColumnName As String: urlColumnName = "I"
    Dim imageColumnName As String: imageColumnName = "O"
    Dim hasImageColumnName As String: hasImageColumnName = "P"
    
    Set url_column = Worksheets(1).Range(urlColumnName & fromRow & ":" & urlColumnName & toRow)
    Set image_column = Worksheets(1).Range(imageColumnName & fromRow & ":" & imageColumnName & toRow)
    Set hasImage_column = Worksheets(1).Range(hasImageColumnName & fromRow & ":" & hasImageColumnName & toRow)
    
    Dim i As Long
    For i = 1 To url_column.Cells.Count
        On Error GoTo NextRow:
               
        Debug.Print (url_column.Cells(i).Value & " " & (fromRow + i - 1))
        
        hasImage = hasImage_column.Cells(i).Value
        
        If hasImage = "1" Then GoTo NextRow
        
        localImagePath = "c:\Temp\xls-" & (fromRow + i - 1) & ".png"
        Call GURoL(url_column.Cells(i).Value, localImagePath)
        
        'Set myPicture = ActiveSheet.Pictures.Insert(localImagePath)
        Set shape = ActiveSheet.Shapes.AddPicture(localImagePath, msoFalse, msoTrue, 0, 0, 100, 100)
                
        With shape
            .Left = image_column.Cells(i).Left
            .Top = image_column.Cells(i).Top
            .Height = 50
            .Width = 50
            image_column.Cells(i).EntireRow.RowHeight = .Height
        End With
        
        hasImage_column.Cells(i).Value = "1"
        
NextRow:
    Resume NextRow2
NextRow2:
    Next
End Sub