images downloading from url in excel and rename it.

[Paya92]

Hello .
I get stuck with this "chinesse" work . i have to download and rename all the pictures from an Excel sheet and i can't figure out how to make it faster. I have tons of rows and it takes me years to do it manually. i've already see a post about this but that doesn't work for me ( or i do something wrong). Can anyone guide me step by step , pls ?... Thank you

 

[sadboy309]

We need information about picture, address of url, website, address of cells on Sheet where it will save. You do not provide enough information for me.

 

[Paya92]

an example of url look like this...https://www.brandsdistribution.com/prod/stock_product_image_99780_1347890710.jpg
what info do you need about the picture? i want it to save it anywhere on the computer, just save. i try to put an exemple of xls file, but with less rows. PLease Help me because i'm stuck from over 1 week

 

[Paya92]

My sheet look like this..
In first column, the name that i wanna call the products, and in the second column is the url . i'm using windows 10 and excel 2016. Please help.

 

[Leith Ross]

Hello Paya92,

Add a new VBA Module to your Workbook and then copy and paste the code below into it. The file name is taken from the URL. You can change the file name to something else if you want to. Ditto for the file path. The macro does not check if the file already exists. If it does, it will overwrite the old file.

' Written:  September 16, 2015
' Author:   Leith Ross
' Summary:  Function downloads a Web site resource to a local disk file.


Private Const E_OUTOFMEMORY             As Long = &H8007000E
Private Const INET_E_DOWNLOAD_FAILURE   As Long = &H800C0008


Private Declare Function URLDownloadToFile _
    Lib "urlmon.dll" Alias "URLDownloadToFileA" _
        (ByVal pCaller As Long, _
         ByVal szURL As String, _
         ByVal szFileName As String, _
         ByVal dwReserved As Long, _
         ByVal lpfnCB As Long) _
    As Long
    
Function DownloadURLtoFile(ByVal URL As String, ByVal FilePath As String) As Boolean


    Dim Msg     As String
    Dim oFolder As Object
    Dim vFolder As Variant
    
        vFolder = Left(FilePath, InStrRev(FilePath, "\"))
        
        With CreateObject("Shell.Application")
            Set oFolder = .Namespace(vFolder)
        End With
    
        If oFolder Is Nothing Then
            MsgBox "Directory Not Found for:" & vbLf & vbLf & FilePath, vbExclamation
            Exit Function
        End If
        
        Select Case URLDownloadToFile(0&, URL, FilePath, 0&, 0&)
            Case 0: Msg = "Download complete!": DownloadURLtoFile = True
            Case E_OUTOFMEMORY: Msg = "Insufficient memory to complete the operation."
            Case INET_E_DOWNLOAD_FAILURE: Msg = "The specified resource or callback interface was invalid."
        End Select
        
        MsgBox Msg
        
End Function

Example of Using the Macro

Sub DownloadTest()


    Dim FileName    As String
    Dim FilePath    As String
    Dim URL         As String
    
        FilePath = "C:\Test"
        
        URL = "https://www.brandsdistribution.com/prod/stock_product_image_99780_1347890710.jpg"
        
        FileName = Right(URL, Len(URL) - InStrRev(URL, "/"))
        FilePath = IIf(Right(FilePath, 1) <> "\", FilePath & "\", FilePath)
        
        Call DownloadURLtoFile(URL, FilePath & FileName)
        
End Sub